日期:2014-05-18  浏览次数:20544 次

关于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


-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'


--不使用union all
;with t
as(
select *,case when 日期>=getdate() then 1 else 0 end as A
from tbl
)

select 日期,备注 from t
order by a,(case when a=1 then 日期 end),
(case when a=0 then 日期 end) desc

/*
日期    备注
2012-03-09    F
2012-03-07    E
2012-03-06    D
2012-03-05    C
2012-03-02    B
2012-03-11    G
2012-03-12    H
2012-03-13    I
2012-03-15    J
2012-03-19    K
2012-03-20    L
*/

估计得用case when 了,这个你看看

------解决方案--------------------
SQL code
SELECT B.AA_ID,B.BB_ID
FROM CC B,(
SELECT BB_ID,MIN(AA.CreateDate) AS DT
FROM CC,AA
WHERE AA.AA_ID =CC.AA_ID
GROUP BY CC.BB_ID
) AS T
WHERE B.BB_ID=T.BB_ID
ORDER 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 c
left join aa a on a.aa_id=c.aa_id
left join bb b on b.bb_id=c.bb_id
order by a.createdate,b.bb_id