# sqlserver查询

0

@心判世界: 哪里不符合 你要写出来。需求写的不够详细。test case举例。

po-AH 3年前

0

USE [MyDatabase]
/*
CREATE TABLE [dbo].question(
NO1 INT NOT NULL,
NAME nvarchar NOT NULL,
NO2 INT NOT NULL,
TYPE INT NOT NULL

)
--DROP TABLE question
INSERT INTO question VALUES (1,'A',5,1)
INSERT INTO question VALUES (2,'B',4,1)
INSERT INTO question VALUES (3,'C',6,1)
INSERT INTO question VALUES (5,'D',1,1)
INSERT INTO question VALUES (7,'E',2,1)
INSERT INTO question VALUES (1,'F',4,2)
INSERT INTO question VALUES (2,'G',4,2)
INSERT INTO question VALUES (2,'H',5,2)
INSERT INTO question VALUES (3,'I',5,2)
INSERT INTO question VALUES (4,'J',6,2)
INSERT INTO question VALUES (5,'K',6,2)
INSERT INTO question VALUES (6,'L',3,2)

*/
SELECT *FROM question

SELECT NO2R,FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NO2 ORDER BY TYPE) AS NO2R,

FROM question --ORDER BY NO2
)T
WHERE NO2R =1
ORDER BY T.NO1

---结果--排序已经正确--
NO2R NO2R NO1 NAME NO2 TYPE
1 1 1 A 5 1
1 1 2 B 4 1
1 1 3 C 6 1
1 1 5 D 1 1
1 1 6 L 3 2
1 1 7 E 2 1

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY T1.NO1) AS order1,NO1,NAME,NO2,TYPE FROM(
SELECT NO2R,NO1,NAME,NO2,TYPE FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NO2 ORDER BY TYPE) AS NO2R,NO1,NAME,NO2,TYPE
FROM question --ORDER BY NO2
)T
WHERE NO2R =1
--ORDER BY T.NO1 <-用partition代替
)T1
)T2
left join
(SELECT NO1,NAME,NO2,TYPE FROM question) T3 ON T3.NO2= T2.NO2

-----结果最右四列-----
order1 NO1 NAME NO2 TYPE NO1 NAME NO2 TYPE
1 1 A 5 1 1 A 5 1
1 1 A 5 1 2 H 5 2
1 1 A 5 1 3 I 5 2
2 2 B 4 1 2 B 4 1
2 2 B 4 1 1 F 4 2
2 2 B 4 1 2 G 4 2
3 3 C 6 1 3 C 6 1
3 3 C 6 1 4 J 6 2
3 3 C 6 1 5 K 6 2
4 5 D 1 1 5 D 1 1
5 6 L 3 2 6 L 3 2
6 7 E 2 1 7 E 2 1

po-A | 菜鸟二级 |园豆：268 | 2020-06-18 22:04

@心判世界: 可能left join筛选掉了，用full join试试

po-A | 园豆：268 (菜鸟二级) | 2020-08-24 12:09

0

SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY Table1.NO1 ) AS order1 ,
NO1 ,NAME ,NO2 ,
TYPE
FROM ( SELECT NO2R , NO1 ,NAME ,NO2 ,
TYPE
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY NO2 ORDER BY TYPE ) AS NO2R ,
NO1 ,NAME ,NO2 ,
TYPE
FROM question
) Tablet
WHERE NO2R = 1
) Table1
) Table2
LEFT JOIN ( SELECT NO1 ,NAME ,NO2 ,TYPE
FROM question
) Table3 ON Table3.NO2 = Table2.NO2

@po-AH: 混个分_

0

0

TYPE可能只有1也可能只有2 也可能有1也有2，不是一定会有1

您需要登录以后才能回答，未注册用户请先注册