关于SQL多重排序,该怎么解决

2012-03-19

关于SQL多重排序现有三个表 aa(aa_ID,CreateDate)bb(bb_ID,createDate)，,cc(iautoID,aa_ID,bb_ID)，cc表中

关于SQL多重排序
现有三个表 aa(aa_ID,CreateDate) bb(bb_ID,createDate)，,cc(iautoID,aa_ID,bb_ID)，cc表中存放二者的对应关系，aa表中的一条记录可能对应bb表中多条记录，bb表中一条记录也可能对应aa表中多条记录，三者做联合查询，现需要输出结果格式为：大体上按aa中的CreateDate排序，但是如果aa中多条数据对应BB中一条，要将aa的信息显示到一块，不知道我说清楚了没有，就是类似这样的格式，
aa1 bb1
aa1 bb2
aa3 bb3
aa3 bb3
aa2 bb4
请问如何实现？

[解决办法]

SQL code

-->生成测试数据:GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE,备注 VARCHAR(100))GOINSERT TBLSELECT '2012-03-02','B' UNION ALLSELECT '2012-03-05','C' UNION ALLSELECT '2012-03-06','D' UNION ALLSELECT '2012-03-07','E' UNION ALLSELECT '2012-03-09','F' UNION ALLSELECT '2012-03-11','G' UNION ALLSELECT '2012-03-12','H' UNION ALLSELECT '2012-03-13','I' UNION ALLSELECT '2012-03-15','J' UNION ALLSELECT '2012-03-19','K' UNION ALLSELECT '2012-03-20','L'--不使用union all;with tas(select *,case when 日期>=getdate() then 1 else 0 end as Afrom tbl)select 日期,备注 from torder by a,(case when a=1 then 日期 end),(case when a=0 then 日期 end) desc/*日期    备注2012-03-09    F2012-03-07    E2012-03-06    D2012-03-05    C2012-03-02    B2012-03-11    G2012-03-12    H2012-03-13    I2012-03-15    J2012-03-19    K2012-03-20    L*/估计得用case when 了，这个你看看
[解决办法]
SQL codeSELECT B.AA_ID,B.BB_IDFROM CC B,(SELECT BB_ID,MIN(AA.CreateDate) AS DTFROM CC,AAWHERE AA.AA_ID =CC.AA_IDGROUP BY CC.BB_ID) AS TWHERE B.BB_ID=T.BB_IDORDER BY T.DT,B.BB_ID
[解决办法]
aa1 bb1
aa1 bb2
aa3 bb3
aa3 bb3
aa2 bb4

结果是

aa1 bb1
aa1 bb5
aa2 bb3
aa4 bb3
aa3 bb4


是这样的需求吗
[解决办法]
SQL code好像比较简单：select *from cc cleft join aa a on a.aa_id=c.aa_idleft join bb b on b.bb_id=c.bb_idorder by a.createdate,b.bb_id

热点排行

SQL Server

关于SQL多重排序,该怎么解决