]
Create Table AA
(
Code Char(10)
,IncDes Char(10)
)
Go
Create Table INGood
(
CODE Char(10)
,INV Char(10)
)
Go
Create Table INGoodDetail
(
INV Char(10)
,NAME Char(10)
,PICE Float
)Go
Insert AA select '1111','A仓库'
Union all select '2222','B仓库'
Go
Insert INGood Select '1111','P010' Union all Select '2222','P020'
Go
Insert INGoodDetail Select 'P010','可乐','10' union all Select 'P010','雪碧','12'
union all Select 'P020','白沙','10' union all Select 'P020','红梅','12' union all Select 'P020','可乐','20'Go
Select Code,inv,name,pice from
(
Select CODE,IncDes as inv,'' as name ,'' as pice,CODE as P1,'' as p2,'' as p3 From aa
union all
Select '' as Code,INV,'' as name ,'' as pice,a.CODE as P1,INV as p2,'' as p3 From aa a ,INGood i where a.code=i.code
union all
Select '' as Code,i.inv,it.name,Convert(Varchar,it.pice)PICE,i.code as p1,i.inv as p2,it.name as p3 from ingood i ,ingooddetail it where i.inv=it.inv
) A
Order by p1,p2,p3
(所影响的行数为 2 行)
(所影响的行数为 2 行)
(所影响的行数为 5 行)Code inv name pice
---------- ---------- ---------- ------------------------------
1111 A仓库
P010
P010 可乐 10
P010 雪碧 12
2222 B仓库
P020
P020 白沙 10
P020 红梅 12
P020 可乐 20(所影响的行数为 9 行)
Create Table AA
(
Code Char(10)
,IncDes Char(10)
)
Go
Create Table INGood
(
CODE Char(10)
,INV Char(10)
)
Go
Create Table INGoodDetail
(
INV Char(10)
,NAME Char(10)
,PICE Float
)Go
Insert AA select '1111','A仓库'
Union all select '2222','B仓库'
Go
Insert INGood Select '1111','P010' Union all Select '2222','P020'
Go
Insert INGoodDetail Select 'P010','可乐','10' union all Select 'P010','雪碧','12'
union all Select 'P020','白沙','10' union all Select 'P020','红梅','12' union all Select 'P020','可乐','20'Go
Select Code,inv,name,pice from
(
Select CODE,IncDes as inv,'' as name ,'' as pice,CODE as P1,'' as p2,'' as p3 From aa
union all
Select '' as Code,INV,'' as name ,'' as pice,a.CODE as P1,INV as p2,'' as p3 From aa a ,INGood i where a.code=i.code
union all
Select '' as Code,i.inv,it.name,Convert(Varchar,it.pice)PICE,i.code as p1,i.inv as p2,it.name as p3 from ingood i ,ingooddetail it where i.inv=it.inv
) A
Order by p1,p2,p3
(所影响的行数为 2 行)
(所影响的行数为 2 行)
(所影响的行数为 5 行)Code inv name pice
---------- ---------- ---------- ------------------------------
1111 A仓库
P010
P010 可乐 10
P010 雪碧 12
2222 B仓库
P020
P020 白沙 10
P020 红梅 12
P020 可乐 20(所影响的行数为 9 行)
--1.1
SELECT
row_number() OVER(PARTITION BY inv ORDER BY inv DESC) AS row,
dense_rank() OVER(ORDER BY inv ) AS rank,
code,
inv,
incdes,
a,
b
INTO #temp
FROM
(
SELECT DISTINCT b.code,
b.inv,
a.IncDes,
null AS a,
null AS b
FROM AA AS A
INNER JOIN INGood AS B
ON B.Code =A.Code
INNER JOIN INGoodDetail AS C
ON C.INV =B.INV
UNION ALL
SELECT NULL,
c.inv,
a.IncDes,
c.[name],
c.pice
FROM AA AS A
INNER JOIN INGood AS B
ON B.Code =A.Code
INNER JOIN INGoodDetail AS C
ON C.INV =B.INV
) AS a
ORDER BY inv DESC,code DESC-----------1.2
SELECT code,
incdes,
b AS [NAME],
c AS price
FROM (
SELECT row,
rank,
code,
incdes,
CAST(a AS CHAR(5)) AS a,
CAST(b AS CHAR(5)) AS b,
NULL AS c
FROM #temp
WHERE row=1
UNION ALL
SELECT row,
rank,
code,
inv,
incdes,
a,
b
FROM #temp
WHERE row>1
) AS a
ORDER BY rank DESC,row --1.3
DROP TABLE #temp
--重新发布下,Sorry!SELECT
row_number() OVER(PARTITION BY inv ORDER BY inv DESC) AS row,
dense_rank() OVER(ORDER BY inv ) AS rank,
code,
inv,
incdes,
a,
b
INTO #temp
FROM
(
SELECT DISTINCT b.code,
b.inv,
a.IncDes,
null AS a,
null AS b
FROM AA AS A
INNER JOIN INGood AS B
ON B.Code =A.Code
INNER JOIN INGoodDetail AS C
ON C.INV =B.INV
UNION ALL
SELECT NULL,
c.inv,
a.IncDes,
c.[name],
c.pice
FROM AA AS A
INNER JOIN INGood AS B
ON B.Code =A.Code
INNER JOIN INGoodDetail AS C
ON C.INV =B.INV
) AS a
ORDER BY inv DESC,code DESC-----------1.2
SELECT code,
incdes,
b AS [NAME],
c AS price
FROM (
SELECT row,
rank,
code,
incdes,
CAST(a AS CHAR(5)) AS a,
CAST(b AS CHAR(5)) AS b,
NULL AS c
FROM #temp
WHERE row=1
UNION ALL
SELECT row,
rank,
code,
inv,
incdes,
a,
b
FROM #temp
WHERE row>1UNION ALL SELECT 1.5 row,
dense_rank() OVER(ORDER BY inv) rank,
NULL code,
inV,
NULL AS a,
NULL AS b,
NULL AS c
FROM #temp
WHERE row=1
) AS a
ORDER BY rank DESC,row --1.3
DROP TABLE #temp
PO10001
PO10001 可乐 10
PO10001 雪碧 12
2222 B仓库
P010002
P010002 白沙 5
P010002 红梅 9
P010002 可乐 20
if object_id('tempdb.dbo.#AA') is not null drop table #AA
create table #AA (Code int,IncDes varchar(5))
insert into #AA
select 1111,'A仓库' union all
select 2222,'B仓库'
--> 测试数据: #INGood
if object_id('tempdb.dbo.#INGood') is not null drop table #INGood
create table #INGood (Code int,INV varchar(7))
insert into #INGood
select 1111,'P010001' union all
select 2222,'P010002'
--> 测试数据: #INGoodDetail
if object_id('tempdb.dbo.#INGoodDetail') is not null drop table #INGoodDetail
create table #INGoodDetail (INV varchar(7),NAME varchar(4),PICE int)
insert into #INGoodDetail
select 'P010001','可乐',10 union all
select 'P010001','雪碧',12 union all
select 'P010002','白沙',5 union all
select 'P010002','红梅',9 union all
select 'P010002','可乐',20select
Code=isnull(ltrim(a.Code),''),
INFO=coalesce(a.IncDes, b.INV, c.INV),
NAME=isnull(c.NAME,''),
PICE=isnull(ltrim(c.PICE),'')
from
#AA as a
full join
#INGood as b
on ltrim(a.code)=ltrim(b.code)+'abc' --> 利用外部联接的特性造成非匹配联接
full join
#INGoodDetail as c
on b.INV=c.INV+'abc' --> 利用外部联接的特性造成非匹配联接
left join
#INGood as d
on a.Code = d.Code
order by
isnull(d.INV,coalesce(a.IncDes, b.INV, c.INV)),d.INV desc/*
Code INFO NAME PICE
------------ ------- ---- ------------
1111 A仓库
P010001
P010001 可乐 10
P010001 雪碧 12
2222 B仓库
P010002
P010002 白沙 5
P010002 红梅 9
P010002 可乐 20
*/
order by
coalesce(d.INV,a.IncDes, b.INV, c.INV),d.INV desc