27,582
社区成员




;WITH t(ID,[NAME],mssql,mysql,sqlite)
AS(
SELECT 1,'aaa',60,70,80 UNION ALL
SELECT 2,'bbb',60,70,80
)
SELECT sv1.number*sv2.number AS ID,t.NAME,p.projec,abs(checksum(newid()))%100 + 1 AS Score
FROM t
INNER JOIN [master].dbo.spt_values AS sv1 ON sv1.[type]='P' AND sv1.number BETWEEN 1 AND 100
INNER JOIN [master].dbo.spt_values AS sv2 ON sv2.[type]='P' AND sv2.number BETWEEN 1 AND 100
CROSS APPLY (VALUES(1,'mssql'),(2,'mysql'),(3,'sqlite')) p(id,projec)
create table test(id int, name varchar(10), mssql int, mysql int, sqlite int)
go
insert into test values(1,'aaa',60,70,80),(2,'bbb',60,70,60)
go
select * from test
go
-- 效率你自己测试一下
select * from test unpivot(project for score in ([mssql],[mysql],[sqlite])) x
go
drop table test
go
(2 行受影响)
id name mssql mysql sqlite
----------- ---------- ----------- ----------- -----------
1 aaa 60 70 80
2 bbb 60 70 60
(2 行受影响)
id name project score
----------- ---------- ----------- --------------------
1 aaa 60 mssql
1 aaa 70 mysql
1 aaa 80 sqlite
2 bbb 60 mssql
2 bbb 70 mysql
2 bbb 60 sqlite
(6 行受影响)
;WITH t(ID,[NAME],mssql,mysql,sqlite)
AS(
SELECT 1,'aaa',60,70,80 UNION ALL
SELECT 2,'bbb',60,70,80
)
SELECT ROW_NUMBER() OVER (ORDER BY t.ID,sv1.number,sv2.number) AS ID,t.NAME,p.projec,abs(checksum(newid()))%100 + 1 AS Score
FROM t
INNER JOIN [master].dbo.spt_values AS sv1 ON sv1.[type]='P' AND sv1.number BETWEEN 1 AND 1000
INNER JOIN [master].dbo.spt_values AS sv2 ON sv2.[type]='P' AND sv2.number BETWEEN 1 AND 1000
LEFT JOIN (VALUES(1,'mssql'),(2,'mysql'),(3,'sqlite')) p(id,projec) ON p.id=abs(checksum(newid()))%3 + 1