我有个表,我想根据里面的字段数目排序,重复的数据越多的就排在最上面,相同的则可以随便排序
问题一:只有一列,按照由多到少排序。CREATE TABLE tb(col1 VARCHAR(50))
INSERT INTO tb(col1)
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH002' UNION ALL SELECT 'YANGSH005' UNION ALL
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH002' UNION ALL SELECT 'YANGSH004' UNION ALL
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH002' UNION ALL SELECT 'YANGSH003' UNION ALL
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH003' UNION ALL SELECT 'YANGSH004'
--排序结果应该是下面的这种形式
col1
YANGSH001
YANGSH001
YANGSH001
YANGSH001
YANGSH002
YANGSH002
YANGSH002
YANGSH003
YANGSH003
YANGSH004
YANGSH004
YANGSH005
--这里003跟004数目一样,就无所谓哪个排在前面了,最好是小的再前面问题二:是问题一的升级,多列,还是按照从多到少排列,各列之间不影响排序,各列排各列的。CREATE TABLE tb(col1 VARCHAR(50),col2 VARCHAR(50),col3 VARCHAR(50))
INSERT INTO tb(col1,col2,col3)
SELECT 'YANGSH001','YANGSH014','YANGSH022' UNION ALL
SELECT 'YANGSH001','YANGSH011','YANGSH022' UNION ALL
SELECT 'YANGSH001','YANGSH011','YANGSH022' UNION ALL
SELECT 'YANGSH002','YANGSH011','YANGSH022' UNION ALL
SELECT 'YANGSH002','YANGSH013','YANGSH021' UNION ALL
SELECT 'YANGSH002','YANGSH013','YANGSH021' UNION ALL
SELECT 'YANGSH002','YANGSH013','YANGSH023' UNION ALL
SELECT 'YANGSH003','YANGSH013','YANGSH023' UNION ALL
SELECT 'YANGSH003','YANGSH012','YANGSH023' UNION ALL
SELECT 'YANGSH004','YANGSH012','YANGSH023'
--排序后应是下面这个样子的
col1 col2 col3
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH001 YANGSH011 YANGSH023
YANGSH001 YANGSH011 YANGSH023
YANGSH001 YANGSH011 YANGSH023
YANGSH003 YANGSH012 YANGSH023
YANGSH003 YANGSH012 YANGSH021
YANGSH004 YANGSH014 YANGSH021
--各个列之间还是由多到少排序,不影响如果我还有更多的列,那么我的问题二应该要怎么写,谢谢大家了!!!!sql排序sql server
问题一:只有一列,按照由多到少排序。CREATE TABLE tb(col1 VARCHAR(50))
INSERT INTO tb(col1)
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH002' UNION ALL SELECT 'YANGSH005' UNION ALL
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH002' UNION ALL SELECT 'YANGSH004' UNION ALL
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH002' UNION ALL SELECT 'YANGSH003' UNION ALL
SELECT 'YANGSH001' UNION ALL SELECT 'YANGSH003' UNION ALL SELECT 'YANGSH004'
--排序结果应该是下面的这种形式
col1
YANGSH001
YANGSH001
YANGSH001
YANGSH001
YANGSH002
YANGSH002
YANGSH002
YANGSH003
YANGSH003
YANGSH004
YANGSH004
YANGSH005
--这里003跟004数目一样,就无所谓哪个排在前面了,最好是小的再前面问题二:是问题一的升级,多列,还是按照从多到少排列,各列之间不影响排序,各列排各列的。CREATE TABLE tb(col1 VARCHAR(50),col2 VARCHAR(50),col3 VARCHAR(50))
INSERT INTO tb(col1,col2,col3)
SELECT 'YANGSH001','YANGSH014','YANGSH022' UNION ALL
SELECT 'YANGSH001','YANGSH011','YANGSH022' UNION ALL
SELECT 'YANGSH001','YANGSH011','YANGSH022' UNION ALL
SELECT 'YANGSH002','YANGSH011','YANGSH022' UNION ALL
SELECT 'YANGSH002','YANGSH013','YANGSH021' UNION ALL
SELECT 'YANGSH002','YANGSH013','YANGSH021' UNION ALL
SELECT 'YANGSH002','YANGSH013','YANGSH023' UNION ALL
SELECT 'YANGSH003','YANGSH013','YANGSH023' UNION ALL
SELECT 'YANGSH003','YANGSH012','YANGSH023' UNION ALL
SELECT 'YANGSH004','YANGSH012','YANGSH023'
--排序后应是下面这个样子的
col1 col2 col3
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH001 YANGSH011 YANGSH023
YANGSH001 YANGSH011 YANGSH023
YANGSH001 YANGSH011 YANGSH023
YANGSH003 YANGSH012 YANGSH023
YANGSH003 YANGSH012 YANGSH021
YANGSH004 YANGSH014 YANGSH021
--各个列之间还是由多到少排序,不影响如果我还有更多的列,那么我的问题二应该要怎么写,谢谢大家了!!!!sql排序sql server
--INSERT INTO tb
-- ( col1
-- )
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH005'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH004'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH003'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH003'
-- UNION ALL
-- SELECT 'YANGSH004'
SELECT col1
FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY GETDATE())id,*
FROM TB)a
GROUP BY col1
ORDER BY MAX(id) DESC
/*
col1
--------------------------------------------------
YANGSH001
YANGSH002
YANGSH003
YANGSH004
YANGSH005(5 行受影响)
*/
--INSERT INTO tb1
-- ( col1
-- )
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH005'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH004'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH003'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH003'
-- UNION ALL
-- SELECT 'YANGSH004'
SELECT a.col1
FROM tb1 a LEFT JOIN (
SELECT *,COUNT(1) [count]
FROM tb1
GROUP BY col1)b ON A.col1=b.col1
ORDER BY b.[count] DESC
/*
col1
--------------------------------------------------
YANGSH001
YANGSH001
YANGSH001
YANGSH001
YANGSH002
YANGSH002
YANGSH002
YANGSH004
YANGSH003
YANGSH003
YANGSH004
YANGSH005(12 行受影响)*/
--INSERT INTO tb(col1,col2,col3)
--SELECT 'YANGSH001','YANGSH014','YANGSH022' UNION ALL
--SELECT 'YANGSH001','YANGSH011','YANGSH022' UNION ALL
--SELECT 'YANGSH001','YANGSH011','YANGSH022' UNION ALL
--SELECT 'YANGSH002','YANGSH011','YANGSH022' UNION ALL
--SELECT 'YANGSH002','YANGSH013','YANGSH021' UNION ALL
--SELECT 'YANGSH002','YANGSH013','YANGSH021' UNION ALL
--SELECT 'YANGSH002','YANGSH013','YANGSH023' UNION ALL
--SELECT 'YANGSH003','YANGSH013','YANGSH023' UNION ALL
--SELECT 'YANGSH003','YANGSH012','YANGSH023' UNION ALL
--SELECT 'YANGSH004','YANGSH012','YANGSH023'
----排序后应是下面这个样子的
-- col1 col2 col3
--YANGSH002 YANGSH013 YANGSH022
--YANGSH002 YANGSH013 YANGSH022
--YANGSH002 YANGSH013 YANGSH022
--YANGSH002 YANGSH013 YANGSH022
--YANGSH001 YANGSH011 YANGSH023
--YANGSH001 YANGSH011 YANGSH023
--YANGSH001 YANGSH011 YANGSH023
--YANGSH003 YANGSH012 YANGSH023
--YANGSH003 YANGSH012 YANGSH021
--YANGSH004 YANGSH014 YANGSH021
SELECT col1,col2,col3
FROM
(SELECT TOP 99.9999 PERCENT a.col1,ROW_NUMBER()OVER (ORDER BY GETDATE())id--,NULL col2,NULL col3
FROM tb a INNER JOIN (
SELECT col1,COUNT(1) [count]
FROM tb
GROUP BY col1)b ON A.col1=b.col1
ORDER BY b.[count] DESC )a
LEFT JOIN
(SELECT TOP 99.9999 PERCENT a.col2,ROW_NUMBER()OVER (ORDER BY GETDATE())id
FROM tb a INNER JOIN (
SELECT col2,COUNT(1) [count]
FROM tb
GROUP BY col2)b ON A.col2=b.col2
ORDER BY b.[count] DESC )b ON b.id=a.id
LEFT JOIN
(SELECT TOP 99.9999 PERCENT a.col3,ROW_NUMBER()OVER (ORDER BY GETDATE())id
FROM tb a INNER JOIN (
SELECT col3,COUNT(1) [count]
FROM tb
GROUP BY col3)b ON A.col3=b.col3
ORDER BY b.[count] DESC )c ON a.id=c.id/*
col1 col2 col3
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH002 YANGSH013 YANGSH022
YANGSH001 YANGSH011 YANGSH023
YANGSH001 YANGSH011 YANGSH023
YANGSH001 YANGSH011 YANGSH023
YANGSH003 YANGSH012 YANGSH023
YANGSH003 YANGSH012 YANGSH021
YANGSH004 YANGSH014 YANGSH021(10 行受影响)
*/
--INSERT INTO tb1
-- ( col1
-- )
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH005'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH004'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH002'
-- UNION ALL
-- SELECT 'YANGSH003'
-- UNION ALL
-- SELECT 'YANGSH001'
-- UNION ALL
-- SELECT 'YANGSH003'
-- UNION ALL
-- SELECT 'YANGSH004'
SELECT a.col1 --,b.COUNT
FROM tb1 a LEFT JOIN (
SELECT col1,mAX([COUNT])[COUNT]
FROM (
SELECT *,ROW_NUMBER()OVER (PARTITION BY col1 ORDER BY GETDATE()) [count]
FROM tb1 ) a GROUP BY col1
)b ON A.col1=b.col1
ORDER BY b.[count]DESC,col1
/*
col1
--------------------------------------------------
YANGSH001
YANGSH001
YANGSH001
YANGSH001
YANGSH002
YANGSH002
YANGSH002
YANGSH003
YANGSH003
YANGSH004
YANGSH004
YANGSH005(12 行受影响)
*/