现有一个表:
tbl:
内容:code name qty other qtys
A AAA 10 001 1
A AAA 10 002 5
A AAA 10 003 4
如何显示:
code name qty other qtys
A AAA 10 001 1
002 5
003 4
tbl:
内容:code name qty other qtys
A AAA 10 001 1
A AAA 10 002 5
A AAA 10 003 4
如何显示:
code name qty other qtys
A AAA 10 001 1
002 5
003 4
写一大串sql,还不如在程序里加几个判断, sql语句主要是数据库的读取
需求有些少见 :)现查出
A AAA 10 第一条记录To Temp再查出前三列为A AAA 10的记录 insert 后两条记录 :)直接联接查询不太可行
Select
(Case When B.code Is Null Then '' Else A.code End) As code,
(Case When B.code Is Null Then '' Else A.name End) As name,
(Case When B.code Is Null Then Null Else A.qty End) As qty,
A.other,
A.qtys
From
tbl A
Left Join
(Select code, name, qty, Min(other) As other From tbl Group By code, name, qty)B
On A.code = B.code And A.name = B.name And A.qty = B.qty And A.other = B.other
Create Table tbl
(code Varchar(10),
name Varchar(10),
qty Int,
other Char(3),
qtys Int)
--插入數據
Insert tbl Select 'A', 'AAA', 10, '001', 1
Union All Select 'A', 'AAA', 10, '002', 5
Union All Select 'A', 'AAA', 10, '003', 4
GO
--測試
Select
(Case When B.code Is Null Then '' Else A.code End) As code,
(Case When B.code Is Null Then '' Else A.name End) As name,
(Case When B.code Is Null Then Null Else A.qty End) As qty,
A.other,
A.qtys
From
tbl A
Left Join
(Select code, name, qty, Min(other) As other From tbl Group By code, name, qty)B
On A.code = B.code And A.name = B.name And A.qty = B.qty And A.other = B.other
GO
--刪除測試環境
Drop Table tbl
--結果
/*
code name qty other qtys
A AAA 10 001 1
NULL 002 5
NULL 003 4
*/
写一大串sql,还不如在程序里加几个判断, sql语句主要是数据库的读取
我觉得对。