只能order by case when 字段满足某个条件 then 1 esle 0 end,case when……
这样拼一下试试喽,如果还不行就给每条记录加个标识列,标识它是
G001 天和可乐、还是A表的记录还是B表的记录,再用case when 拼一下。
这样拼一下试试喽,如果还不行就给每条记录加个标识列,标识它是
G001 天和可乐、还是A表的记录还是B表的记录,再用case when 拼一下。
调试欢乐多
insert GetGoods select 1,' IN07010009','G001','天和可乐','月结','2007/01/09',900
union all select 2,' IN07010010','G003','天虹制衣','月结','2007/01/09',448
union all select 3,' IN07010011','G003','天虹制衣','月结','2007/01/08',22
union all select 4,' IN07010012','G003','天虹制衣','现金','2007/01/10',36
union all select 5,' IN07010013','G003','天虹制衣','现金','2007/01/09',92
create table PayGoods(id int,code varchar(20),datas datetime,numb varchar(10),numbname varchar(50),mons money)
insert PayGoods select 1,' IN07010009','2007/01/09','G001','天和可乐',900
union all select 2,' IN07010010','2007/01/09','G003','天虹制衣',200
union all select 3,' IN07010010','2007/01/09','G003','天虹制衣',248
union all select 4,' IN07010011','2007/01/08','G003','天虹制衣',22select identity(int,1,1) id,* into # from
(
select numb,numbname,datas,mons,type,code from GetGoods
union
select numb,numbname,datas,mons,null type,code from PayGoods
)A order by numb,numbname,datas,code,type descselect case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname order by id) then numb else '' end numb,
case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname order by id) then numbname else '' end numb,
case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname and datas=a.datas and code=a.code order by id) then datas else null end datas,
datas,
mons,type,code
from # adrop table GetGoods,#,PayGoods
insert GetGoods select 1,' IN07010009','G001','天和可乐','月结','2007/01/09',900
union all select 2,' IN07010010','G003','天虹制衣','月结','2007/01/09',448
union all select 3,' IN07010011','G003','天虹制衣','月结','2007/01/08',22
union all select 4,' IN07010012','G003','天虹制衣','现金','2007/01/10',36
union all select 5,' IN07010013','G003','天虹制衣','现金','2007/01/09',92
create table PayGoods(id int,code varchar(20),datas datetime,numb varchar(10),numbname varchar(50),mons money)
insert PayGoods select 1,' IN07010009','2007/01/09','G001','天和可乐',900
union all select 2,' IN07010010','2007/01/09','G003','天虹制衣',200
union all select 3,' IN07010010','2007/01/09','G003','天虹制衣',248
union all select 4,' IN07010011','2007/01/08','G003','天虹制衣',22select identity(int,1,1) id,* into # from
(
select numb,numbname,datas,mons,type,code from GetGoods
union
select numb,numbname,datas,mons,null type,code from PayGoods
)A order by numb,numbname,datas,code,type descselect case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname order by id) then numb else '' end numb,
case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname order by id) then numbname else '' end numb,
case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname and datas=a.datas and code=a.code order by id) then convert(varchar(10),datas,111) else null end datas,
mons,type,code
from # adrop table GetGoods,#,PayGoods
CASE sort WHEN 1 THEN NULL ELSE datas END AS datas1,
mons,type,code
FROM
(SELECT numb,numbname,
CONVERT(NVARCHAR(20),datas,111) AS datas,
mons,type,code, 0 AS sort,id
FROM GetGoods
UNION ALL
SELECT numb,numbname,
CONVERT(NVARCHAR(20),datas,111),
mons,NULL ,code, 1 AS sort,id
FROM PayGoods) a
ORDER BY numb,datas,code,sort,id
numb numbname datas1 mons type code
---------- -------------------------------------------------- -------------------- --------------------- -------------------- --------------------
G001 天和可乐 2007/01/09 900.0000 月结 IN07010009
G001 天和可乐 NULL 900.0000 NULL IN07010009
G003 天虹制衣 2007/01/08 22.0000 月结 IN07010011
G003 天虹制衣 NULL 22.0000 NULL IN07010011
G003 天虹制衣 2007/01/09 448.0000 月结 IN07010010
G003 天虹制衣 NULL 200.0000 NULL IN07010010
G003 天虹制衣 NULL 248.0000 NULL IN07010010
G003 天虹制衣 2007/01/09 92.0000 现金 IN07010013
G003 天虹制衣 2007/01/10 36.0000 现金 IN07010012(9 row(s) affected)
(
select numb,numbname,datas,mons,type,code from GetGoods
union
select numb,numbname,datas,mons,null type,code from PayGoods
)A order by numb,numbname,datas,code,type desc
你的语句在排列方面是对了,可否能再修改一下变成我例子中的结果,小弟不才,用你的语句试着改不成,请指点,3Q
G003 天虹制衣
這兩個一個人啊,把樓主蒙了:)
那改一下语句;
select identity(int,1,1) id,* into # from
(
select numb,numbname,datas,mons,type,code from GetGoods
union
select numb,numbname,datas,mons,null type,code from PayGoods
union
select numb,numbname,null,null,'','' from GetGoods group by numb,numbname
)A order by numb,numbname,datas,code,type desc不过datetime与money类型下显示不出来空;如果楼主一定要这种格式的话,那楼主不妨改一下数据类型
insert GetGoods select 1,' IN07010009','G001','天和可乐','月结','2007/01/09','900'
union all select 2,' IN07010010','G003','天虹制衣','月结','2007/01/09','448'
union all select 3,' IN07010011','G003','天虹制衣','月结','2007/01/08','22.5'
union all select 4,' IN07010012','G003','天虹制衣','现金','2007/01/10','36'
union all select 5,' IN07010013','G003','天虹制衣','现金','2007/01/09','92'
create table PayGoods(id int,code varchar(20),datas varchar(10),numb varchar(10),numbname varchar(50),mons varchar(20))
insert PayGoods select 1,' IN07010009','2007/01/09','G001','天和可乐','900'
union all select 2,' IN07010010','2007/01/09','G003','天虹制衣','200'
union all select 3,' IN07010010','2007/01/09','G003','天虹制衣','248'
union all select 4,' IN07010011','2007/01/08','G003','天虹制衣','22.5'select identity(int,1,1) id,* into # from
(
select numb,numbname,datas,mons,type,code from GetGoods
union
select numb,numbname,datas,mons,null type,code from PayGoods
union
select numb,numbname,'','','','' from GetGoods group by numb,numbname
)A order by numb,numbname,datas,code,type descselect case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname order by id) then numb else '' end numb,
case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname order by id) then numbname else '' end numbname,
case when id=(select top 1 id from # where numb=a.numb and numbname=a.numbname and datas=a.datas and code=a.code order by id) then convert(varchar(10),datas,111) else null end datas,
mons,type,code
from # adrop table GetGoods,#,PayGoods但你在取datas与mons字段时,可强制转换一下类型
insert GetGoods select 1,' IN07010009','G001','天和可乐','月结','2007/01/09',900
union all select 2,' IN07010010','G003','天虹制衣','月结','2007/01/09',448
union all select 3,' IN07010011','G003','天虹制衣','月结','2007/01/08',22
union all select 4,' IN07010012','G003','天虹制衣','现金','2007/01/10',36
union all select 5,' IN07010013','G003','天虹制衣','现金','2007/01/09',92
create table PayGoods(id int,code varchar(20),datas datetime,numb varchar(10),numbname varchar(50),mons money)
insert PayGoods select 1,' IN07010009','2007/01/09','G001','天和可乐',900
union all select 2,' IN07010010','2007/01/09','G003','天虹制衣',200
union all select 3,' IN07010010','2007/01/09','G003','天虹制衣',248
union all select 4,' IN07010011','2007/01/08','G003','天虹制衣',22
GOSELECT (CASE WHEN b.numbname='temp' then a.numb else NULL END),
(CASE WHEN b.numbname='temp' then a.numbname else NULL END),
CASE sort WHEN 1 THEN NULL ELSE datas END AS datas1,
mons,type,code
FROM
(SELECT DISTINCT numb,numbname FROM GetGoods) a RIGHT JOIN
(SELECT numb,numbname,
CONVERT(NVARCHAR(20),datas,111) AS datas,
mons,type,code, 1 AS sort,id
FROM GetGoods
UNION ALL
SELECT numb,numbname,
CONVERT(NVARCHAR(20),datas,111),
mons,NULL ,code, 2 AS sort,id
FROM PayGoods
UNION ALL SELECT DISTINCT numb,'temp',NULL,NULL,NULL,NULL,-1,NULL FROM GetGoods
) b
ON a.numb=b.numb
ORDER BY a.numb,b.datas,code,sort,id
datas1 mons type code
------ ----------- ------------- ---------- --------- ----------
G001 天和可乐 NULL NULL NULL NULL
NULL NULL NULL 900.00 月结 IN07010009
NULL NULL 2007/01/09 900.00 NULL IN07010009
G003 天虹制衣 NULL NULL NULL NULL
NULL NULL NULL 22.00 月结 IN07010011
NULL NULL 2007/01/08 22.00 NULL IN07010011
NULL NULL NULL 448.00 月结 IN07010010
NULL NULL 2007/01/09 200.00 NULL IN07010010
NULL NULL 2007/01/09 248.00 NULL IN07010010
NULL NULL NULL 92.00 现金 IN07010013
NULL NULL NULL 36.00 现金 IN07010012
你的语句在排列方面是对了,可否能再修改一下变成我例子中的结果,小弟不才,用你的语句试着改不成,请指点,3Q---------------------------------------------------SELECT
CASE WHEN mons IS NULL THEN numbs ELSE '' END AS numb,
CASE WHEN mons IS NULL THEN numbname ELSE '' END AS numbname,
CASE sort WHEN 1 THEN NULL ELSE ISNULL(datas,'') END AS datas1,
mons,ISNULL(type,'') AS type,ISNULL(code,'') AS code
FROM
(
SELECT numb AS numbs,numbname,
CONVERT(NVARCHAR(10),datas,111) AS datas,
mons,type,code, 0 AS sort,id
FROM GetGoods
UNION ALL
SELECT numb,numbname,
CONVERT(NVARCHAR(10),datas,111),
mons,NULL ,code, 1 AS sort,id
FROM PayGoods
UNION ALL
(SELECT DISTINCT numb,numbname,NULL ,NULL ,NULL ,NULL ,NULL, NULL
FROM GetGoods
UNION
SELECT DISTINCT numb,numbname,NULL ,NULL ,NULL ,NULL ,NULL, NULL
FROM PayGoods)
) a
ORDER BY numbs,datas,code,sort,idnumb numbname datas1 mons type code
---- ---------- ---------- --------------------- ---------- --------------------
G001 天和可乐 NULL
2007/01/09 900.0000 月结 IN07010009
NULL 900.0000 IN07010009
G003 天虹制衣 NULL
2007/01/08 22.0000 月结 IN07010011
NULL 22.0000 IN07010011
2007/01/09 448.0000 月结 IN07010010
NULL 200.0000 IN07010010
NULL 248.0000 IN07010010
2007/01/09 92.0000 现金 IN07010013
2007/01/10 36.0000 现金 IN07010012(11 row(s) affected)
UNION
SELECT DISTINCT numb,numbname,NULL ,NULL ,NULL ,NULL ,NULL, NULL
FROM PayGoods)
SELECT DISTINCT numb,numbname,NULL ,NULL ,NULL ,NULL ,NULL, NULL
FROM GetGoods
和
SELECT DISTINCT numb,numbname,NULL ,NULL ,NULL ,NULL ,NULL, NULL
FROM PayGoods