基于优先级
一列中选择值我有一个表像这样基于优先级
AMID Entry ------- --------- 1000 MARS 1001 JUPITER 1002 SATURN 1003 VENUS 1003 SATURN 1004 NEPTUNE 1004 SATURN 1005 JUPITER 1005 MARS
现在我想提取DISTINCT AMID特别ENTRY值的值。 茶几应该是这样的
AMID Entry -------- -------- 1000 MARS 1001 JUPITER 1002 SATURN 1003 VENUS 1004 SATURN 1005 MARS
选择项值,则条件,
它采取基于这个优先的价值观:
- VENUS
- MARS
- JUPITER
- SATURN
- NEPTUNE
所以如果有任何阿米德已经金星和火星都,它应该冒金星
火星和土星,它应该采取MARS
海王星和木星,它应该采取JUPITER。(按优先级选择)。
你可以用排序函数做到这一点(如果你使用SQL Server 2008):
DECLARE @t TABLE (AMID INT, Entry VARCHAR(100))
INSERT @t
VALUES
( 1000 ,'MARS'),
( 1001 ,'JUPITER'),
( 1002 ,'SATURN'),
( 1003 ,'VENUS'),
( 1003 ,'SATURN'),
( 1004 ,'NEPTUNE'),
( 1004 ,'SATURN'),
( 1005 ,'JUPITER'),
( 1005 ,'MARS')
;WITH a AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY AMID ORDER BY
CASE Entry
WHEN 'VENUS' THEN 0
WHEN 'MARS' THEN 1
WHEN 'JUPITER' THEN 2
WHEN 'SATURN' THEN 3
WHEN 'NEPTUNE' THEN 4
END) num
FROM @t
)
SELECT AMID, Entry
FROM a
WHERE num = 1
很好的答案。我做了一个SQL小提琴链接这个http://sqlfiddle.com/#!3/d41d8/3207 – MakkyNZ 2012-08-09 09:44:38
@MakkyNZ谢谢:) – 2012-08-09 10:03:33
试试这个MySQL查询:
SELECT AMID, (CASE MIN(Entry) WHEN 1 THEN "VENUS"
WHEN 2 THEN "MARS"
WHEN 3 THEN "JUPITER"
WHEN 4 THEN "SATURN"
WHEN 5 THEN "NEPTUNE"
END) AS Entry
FROM (
SELECT AMID, (CASE Entry WHEN "VENUS" THEN 1
WHEN "MARS" THEN 2
WHEN "JUPITER" THEN 3
WHEN "SATURN" THEN 4
WHEN "NEPTUNE" THEN 5
END) AS Entry
FROM table_name
) a
GROUP BY AMID;
试试这个:
;WITH CTE AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY AMID ORDER BY T.PRIORITY) AS ROWNUM
FROM PLANETS P
JOIN
(
SELECT 1 PRIORITY, 'VENUS' AS NAME UNION ALL
SELECT 2, 'MARS' UNION ALL
SELECT 3, 'JUPITER' UNION ALL
SELECT 4, 'SATURN' UNION ALL
SELECT 5, 'NEPTUNE')T
ON T.NAME=P.[ENTRY]
)SELECT AMID,[ENTRY] FROM CTE WHERE ROWNUM=1
你为什么不干脆把项值在自己的表:
CREATE TABLE entry_value
(
entry VARCHAR(10) NOT NULL PRIMARY KEY,
value INT NOT NULL
);
INSERT INTO entry_value VALUES
('VENUS', 0),
('MARS', 1),
('JUPITER', 2),
('SATURN', 3),
('NEPTUNE', 4)
;
然后找到你想要的东西很简单:
SELECT
a.amid,
(SELECT entry FROM entry_value where value = MIN(e.value)) AS entry
FROM amid a
JOIN entry_value e
ON e.entry = a.entry
GROUP BY a.amid
ORDER BY a.amid;
而作为额外的奖励,你可以添加一个外键入口栏,所以人们不能把像1000, 'PLUTO'
这样的垃圾放在那里。
谢谢队友:) – harry 2012-08-09 09:59:07
MySql或Sql Server? – 2012-08-09 09:25:59
这是功课吗? – podiluska 2012-08-09 09:30:09
@ Podiluska:没有配偶:) – harry 2012-08-09 09:57:39