SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM ASELECT T.KeyID ,Column1, Column2 , Column3, Column4 Column5 Column6 FROM #TB T JOIN B ON T.KeyID=B.KeyID AND T.ID IN (SELECT MIN(ID) FROM #TB T1 GROUP BY T.KeyID )
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM ASELECT T.KeyID ,Column1, Column2 , Column3, Column4 Column5 Column6 FROM #TB T JOIN B ON T.KeyID=B.KeyID AND T.ID IN (SELECT TOP 1 MIN(ID) FROM #TB T1 GROUP BY T.KeyID )稍微改下
-->测试表A CREATE TABLE #A (KeyID INT,Column1 VARCHAR(20),Column2 VARCHAR(20),Column3 VARCHAR(20)) INSERT INTO #A SELECT 1,'规格一','门幅一','一等品' UNION ALL SELECT 1,'规格一','门幅二','一等品' UNION ALL SELECT 1,'规格二','门幅二','二等品' UNION ALL SELECT 2,'规格一','门幅一','一等品' UNION ALL SELECT 2,'规格二','门幅二','一等品' UNION ALL SELECT 3,'规格一','门幅一','二等品' -->测试表B CREATE TABLE #B (KeyID INT,Column4 INT,Column5 INT,Column6 INT) INSERT INTO #B SELECT 1,100,200,200 UNION ALL SELECT 2,150,203,450 UNION ALL SELECT 3,500,600,700 -->查询 select a.KeyID,a.Column1,a.Column2,a.Column3,b.Column4,b.Column5,b.Column6 from #A a join #B b on a.KeyID=b.KeyID and a.Column2='门幅一' union all select a.KeyID,a.Column1,a.Column2,a.Column3,0,0,0 from #A a join #B b on a.KeyID=b.KeyID and a.Column2='门幅二' order by 1,2 desc,4 desc -->结果 /* KeyID Column1 Column2 Column3 Column4 Column5 Column6 1 规格一 门幅一 一等品 100 200 200 1 规格一 门幅二 一等品 0 0 0 1 规格二 门幅二 二等品 0 0 0 2 规格一 门幅一 一等品 150 203 450 2 规格二 门幅二 一等品 0 0 0 3 规格一 门幅一 二等品 500 600 700 */
CREATE TABLE TBTEST(KeyID INT,Column1 VARCHAR(10),Column2 VARCHAR(10), Column3 VARCHAR(10)) INSERT TBTEST SELECT 1 , '规格一' , '门幅一' , '一等品' UNION ALL SELECT 1 , '规格一' , '门幅二' , '一等品' UNION ALL SELECT 1 , '规格二' , '门幅二' , '二等品' UNION ALL SELECT 2 , '规格一' , '门幅一' , '一等品' UNION ALL SELECT 2 , '规格二' , '门幅二' , '一等品' UNION ALL SELECT 3 , '规格一' , '门幅一' , '二等品' CREATE TABLE TBTEST1(KeyID INT,Column4 INT,Column5 INT, Column6 INT) INSERT TBTEST1 SELECT 1 , 100 , 200 , 200 UNION ALL SELECT 2 , 150 , 203 , 450 UNION ALL SELECT 3 , 500 , 600 , 700 --SELECT * FROM TBTEST --SELECT * FROM TBTEST1 --SELECT * FROM #TB SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM TBTESTSELECT T.KeyID ,Column1, Column2 , Column3, ISNULL(Column4,0)Column4 , ISNULL(Column5,0)Column5 ,ISNULL(Column6,0)Column6 FROM #TB T LEFT JOIN TBTEST1 T2 ON T.KeyID=T2.KeyID AND T.ID IN (SELECT MIN(ID) FROM #TB T1 GROUP BY T1.KeyID )KeyID Column1 Column2 Column3 Column4 Column5 Column6 ----------- ---------- ---------- ---------- ----------- ----------- ----------- 1 规格一 门幅一 一等品 100 200 200 1 规格一 门幅二 一等品 0 0 0 1 规格二 门幅二 二等品 0 0 0 2 规格一 门幅一 一等品 150 203 450 2 规格二 门幅二 一等品 0 0 0 3 规格一 门幅一 二等品 500 600 700(所影响的行数为 6 行)
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM ASELECT T.KeyID ,Column1, Column2 , Column3, Column4 Column5 Column6
FROM #TB T JOIN B ON T.KeyID=B.KeyID AND T.ID IN (SELECT TOP 1 MIN(ID) FROM #TB T1 GROUP BY T.KeyID )稍微改下
CREATE TABLE #A (KeyID INT,Column1 VARCHAR(20),Column2 VARCHAR(20),Column3 VARCHAR(20))
INSERT INTO #A
SELECT 1,'规格一','门幅一','一等品' UNION ALL
SELECT 1,'规格一','门幅二','一等品' UNION ALL
SELECT 1,'规格二','门幅二','二等品' UNION ALL
SELECT 2,'规格一','门幅一','一等品' UNION ALL
SELECT 2,'规格二','门幅二','一等品' UNION ALL
SELECT 3,'规格一','门幅一','二等品'
-->测试表B
CREATE TABLE #B (KeyID INT,Column4 INT,Column5 INT,Column6 INT)
INSERT INTO #B
SELECT 1,100,200,200 UNION ALL
SELECT 2,150,203,450 UNION ALL
SELECT 3,500,600,700
-->查询
select a.KeyID,a.Column1,a.Column2,a.Column3,b.Column4,b.Column5,b.Column6
from #A a
join #B b on a.KeyID=b.KeyID and a.Column2='门幅一'
union all
select a.KeyID,a.Column1,a.Column2,a.Column3,0,0,0
from #A a
join #B b on a.KeyID=b.KeyID and a.Column2='门幅二'
order by 1,2 desc,4 desc
-->结果
/*
KeyID Column1 Column2 Column3 Column4 Column5 Column6
1 规格一 门幅一 一等品 100 200 200
1 规格一 门幅二 一等品 0 0 0
1 规格二 门幅二 二等品 0 0 0
2 规格一 门幅一 一等品 150 203 450
2 规格二 门幅二 一等品 0 0 0
3 规格一 门幅一 二等品 500 600 700
*/
INSERT TBTEST
SELECT 1 , '规格一' , '门幅一' , '一等品' UNION ALL
SELECT 1 , '规格一' , '门幅二' , '一等品' UNION ALL
SELECT 1 , '规格二' , '门幅二' , '二等品' UNION ALL
SELECT 2 , '规格一' , '门幅一' , '一等品' UNION ALL
SELECT 2 , '规格二' , '门幅二' , '一等品' UNION ALL
SELECT 3 , '规格一' , '门幅一' , '二等品' CREATE TABLE TBTEST1(KeyID INT,Column4 INT,Column5 INT, Column6 INT)
INSERT TBTEST1
SELECT 1 , 100 , 200 , 200 UNION ALL
SELECT 2 , 150 , 203 , 450 UNION ALL
SELECT 3 , 500 , 600 , 700 --SELECT * FROM TBTEST
--SELECT * FROM TBTEST1
--SELECT * FROM #TB
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM TBTESTSELECT T.KeyID ,Column1, Column2 , Column3, ISNULL(Column4,0)Column4 , ISNULL(Column5,0)Column5 ,ISNULL(Column6,0)Column6
FROM #TB T LEFT JOIN TBTEST1 T2 ON T.KeyID=T2.KeyID AND T.ID IN (SELECT MIN(ID) FROM #TB T1 GROUP BY T1.KeyID )KeyID Column1 Column2 Column3 Column4 Column5 Column6
----------- ---------- ---------- ---------- ----------- ----------- -----------
1 规格一 门幅一 一等品 100 200 200
1 规格一 门幅二 一等品 0 0 0
1 规格二 门幅二 二等品 0 0 0
2 规格一 门幅一 一等品 150 203 450
2 规格二 门幅二 一等品 0 0 0
3 规格一 门幅一 二等品 500 600 700(所影响的行数为 6 行)
其实,我想要得到得结果是 如果 KeyID 相同,那么 第二个的 Column3,Column4,Column5,Column6 都是 0