问一个查询解决思路

问一个查询
有两个表为A，B

SQL code

Aa1,b1,c1a2,b2,c2a3,b3,c3 Ba1,d1,e1a1,d2,e2a2,d3,e3a2,d4,e4已知d2>d1,d4>d3要得到a1,d1,e1,c1a2,d3,e3,c2  給个sql语句吧

SQL code

--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([a] VARCHAR(2),[b] VARCHAR(2),[c] VARCHAR(2))INSERT [ta]SELECT 'a1','b1','c1' UNION ALLSELECT 'a2','b2','c2' UNION ALLSELECT 'a3','b3','c3'--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([a] VARCHAR(2),[b] VARCHAR(2),[c] VARCHAR(2))INSERT [tb]SELECT 'a1','d1','e1' UNION ALLSELECT 'a1','d2','e2' UNION ALLSELECT 'a2','d3','e3' UNION ALLSELECT 'a2','d4','e4'--------------开始查询--------------------------SELECT b.*,a.c FROM [tb] b INNER JOIN [ta] aON a.a=b.aAND NOT EXISTS(SELECT 1 FROM [tb] WHERE a=b.a AND b<b.b)----------------结果----------------------------/* a    b    c    c---- ---- ---- ----a1   d1   e1   c1a2   d3   e3   c2(2 行受影响)*/
[解决办法]
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')BEGIN    DROP TABLE AENDGOCREATE TABLE A(    col1 INT,    col2 INT,    col3 INT)GOINSERT INTO ASELECT 1,1,1 UNIONSELECT 2,2,2 UNIONSELECT 3,3,3GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'B')BEGIN    DROP TABLE BENDGOCREATE TABLE B(    col1 INT,    col2 INT,    col3 INT)GOINSERT INTO BSELECT 1,1,1 UNIONSELECT 1,2,2 UNION SELECT 2,1,1 UNIONSELECT 2,2,2GOSELECT A.col1,B.col2,B.col3,A.col3FROM B,AWHERE B.col2 <= ALL(SELECT col2 FROM B AS C WHERE B.col1 = C.col1) AND A.col1 = B.col1
[解决办法]
SQL code
--> 测试数据：[A1]if object_id('[A1]') is not null drop table [A1]create table [A1]([cola] varchar(2),[colb] varchar(2),[colc] varchar(2))insert [A1]select 'a1','b1','c1' union allselect 'a2','b2','c2' union allselect 'a3','b3','c3'--> 测试数据：[B2]if object_id('[B2]') is not null drop table [B2]create table [B2]([cola] varchar(2),[colb] varchar(2),[colc] varchar(2))insert [B2]select 'a1','d1','e1' union allselect 'a1','d2','e2' union allselect 'a2','d3','e3' union allselect 'a2','d4','e4'select a.cola,b.colb,b.colc,a.colc from [A1] a inner join [B2] bon a.cola=b.colawhere not exists(select 1 from [B2] c where b.cola=c.cola and c.colb<b.colb and c.colc<b.colc)/*cola    colb    colc    colca1    d1    e1    c1a2    d3    e3    c2*/