我在表格中有三列:
score status No.
1, 2, 1
0, 1, 2
0, 0, 1
我需要这个来编写 C# 风格的伪 SQL:
rows = empty;
rows = "SELECT * FROM `table` WHERE score = 1"
if (rows.Count > 0) //at least one row
return rows;
rows = "SELECT * FROM `table` WHERE status = 2"
if (rows.Count > 1) //more than one row
return row with MAX(No.) from rows; //ie MAX(No.) where status = 2
return rows;
我希望我能说清楚。简而言之,从我的表记录中选择 score = 1 的记录,如果没有这样的记录,则返回状态 = 2 的记录,如果有多个状态 = 2 的记录,则返回最大值为 No 的记录。其中状态 = 2(如果根本没有状态 = 2 的记录,则返回空(。
如何在一个查询中编写它?这对我来说应该是一次很好的学习经历。否则,我知道分解成更小的查询并运行每个查询。而且我现在不能使用存储过程。
编辑:实际上我的查询将有更多的WHERE子句,但在两个条件下相同,这就是我省略它的原因。因此,关于第一个条件,目前只返回一条记录。也就是说SELECT * FROM table WHERE score = 1
现在只会返回一行。我需要/我也会接受提供这种解决方案的答案。但关键是你永远不知道,可能将来会有一些设计变化,可能会有更多的行与score = 1
.这就是为什么我选择记录而不是记录。但理想情况下,业务逻辑是将所有记录都包含score = 1
。目前,记录就可以了。我只是在想,如果只返回一行并且我的队友可以轻松吸收代码,查询会简单得多。
最后更新:谢谢大家,你们一直很善良:)许多答案都很好用,选择一个真的非常令人生畏。我对答案的发现:
一直有效的答案:@GordonLinoff的,@ZaneBiens的,@ZaneBien的,@Scen的,@JulienCh的(最后3个基本相同,但我并不完全了解这些是如何工作的:)(
仅当第一个条件
score = 1
只返回一行时才有效的答案:@HannoBinder的、@ShlomiNoach的、@DaniellePaquette-哈维的。目前,我将坚持使用 @Danielle的(这非常简单(,如果需要多行,稍后会恢复(其余的答案,我无法测试,因为它们要么不是很具体,要么与MySQL无关。
@MatthewPK在上下文中是不合适的。
奖励赏金并接受答案很难,因为有这么多正确答案。我选择这个是因为我觉得它可能也更有效率和可读性,但我会接受@Scen的答案,因为它非常简单。
这似乎很难,因为在这两种情况下您返回的行数是可变的。
以下查询合并所有可能的行,然后检查是否存在第一种类型以查看应返回哪种类型。
select *
from ((SELECT 'SCORE' as matchtype, t.*
FROM `table` t
WHERE score = 1
) union all
(SELECT 'STATUS' as matchtype, t.*
FROM `table` t join
(select max(`No.`) as maxno
from `table`
WHERE status = 2
) tsum
on t.`No.` = tsum.maxno
WHERE status = 2
)
) t cross join
(select count(*) as cnt
from `table`
where score = 1
) const
where matchtype = (case when const.cnt > 0 then 'SCORE' else 'STATUS' end)
注意:返回集包括匹配类型。 要摆脱这种情况,您需要包含所需列的完整列表。
此查询将解决您的问题:
SELECT *
FROM `your_table`
WHERE
status = 2 OR score = 1
ORDER BY
if(score = 1, 1, 0) DESC,
if(status = 2, 1, 0) DESC,
`No.` DESC
LIMIT 1;
与其他答案相反:
- 它不假定列的正值
No.
- 这是MySQL语法(您已标记为
mysql
(
但请随意将功劳/声誉归功于 KSiimson,他铺设了正确的道路。不幸的是,他的查询有两个错误。
另请注意,所述查询不能在 ORDER BY
子句上使用任何索引,因为我们用函数包装列。由于OR
条件,不太可能在FROM clause
上使用索引。
这是一个更有效的解决方案,应该适合您:
SELECT * FROM tbl WHERE score = 1
UNION ALL
(
SELECT a.*
FROM tbl a
INNER JOIN
(
SELECT COUNT(1) AS s1exists FROM tbl WHERE score = 1
) b ON b.s1exists = 0
WHERE a.status = 2
ORDER BY a.no DESC
LIMIT 1
)
故障:
此查询的工作原理是,UNION
中的第一个SELECT
返回 score
= 1 的所有行。第二个SELECT
返回最大值行(基于 no
字段(,其中 status
= 2 并且没有一行其中 score
= 1。无论只有一行还是多行,其中 status
= 2,最大行对于两者都是相同的。
这满足了您指定的所有条件:
- 存在一行或多行,其中
score
= 1:第一个SELECT
返回所有这些行;第二个SELECT
返回空(即使存在status
= 2 的行(。
- 不存在
score
= 1 的行,并且有一行status
= 2:第一个SELECT
返回空;第二个SELECT
返回一行(与最大行相同(。
- 不存在
score
= 1 的行,并且有多个行其中status
= 2:第一个SELECT
返回空;第二个SELECT
返回status
= 2 的最大行。
- 根本不
- 存在任何行,其中
score
= 1 或status
= 2:第一行不返回任何内容;第二行也不返回任何内容,呈现空结果。
完全未经测试,很可能效率很低,但可能有效:
SELECT * FROM `table`
WHERE score=1
union all
SELECT * FROM `table`
WHERE status=2
and NOT EXISTS (SELECT * FROM `table` WHERE score=1)
and `No.` = (
SELECT max(`No.`) FROM `table`
WHERE status=2
)
Select *
From scores
Where score = 1
UNION
(
Select *
From scores
Where status = 2 and not exists (Select * from scores where score = 1)
Order By `No.` desc
Limit 1
);
我table
命名为scores
。
SqlFiddle(使用一些假模式进行查询,以便您可以看到结果(:http://sqlfiddle.com/#!2/6aac2/1/0
编辑:编辑了SQlFiddle以将假模式与问题中的模式相匹配。
假设在No.
中只使用正整数:
select *
from `table`
where status = 2 or score = 1
order by if(score = 1, -2, if(status = 2, -1)), `No.` desc
limit 1;
我所做的是创建一个表达式,行将按其排序。如果分数为 1,则表达式将返回 -2,当只有状态为 1 时,表达式将返回 -1。作为后备,我们按No.
字段降序排序。
请参阅 Shlomi Noach 的解决方案,了解在字段具有负值时No.
有效改进。
我留下了原来的答案,但它不如下面的有效:
/*
Query returns rows with score=1 when they exist or nothing
*/
select score, status, no from temp where score=1
UNION
/*
Query returns rows with status=2 when they exist and when there are no rows
with score=1 or nothing otherwise.
*/
(
select t1.score, t1.status, t1.no from
( /* <subquery> */
(
select score, status, no from temp
where status=2 and (select max(score) from temp where score=1) is null
) as t1
join
(
select max(no) as maxNo from temp where status=2
) as t2
on t1.no=t2.maxNo
) /* </subquery> */
limit 1
)
您可以使用索引来提高效率。另外,我不确定条件语句中的查询(检查是否有任何 score=1 的行(是执行一次还是多次。为了安全起见,您可以将其替换为 @c 并在行前加上:
set @c:=(select max(score) from temp where score=1);
原答案:
/*
Query returns rows with score=1 when they exist or nothing
*/
select score, status, no from temp where score=1
UNION
/*
Query returns nothing when there are rows with score=1,
rows with status=2 when they exist and when there are no rows with score=1, or
nothing otherwise.
Does this by adding weights to rows.
Rows with score=1 have greater weights than rows with status=2.
Joins rows with greatest weights to rows with status=2.
*/
( /* <query> */
select t.score, t.status, t.no from
( /* <joins> */
/* Gets greatest weight */
(
select max(if(score=1, 2, if(status=2, 1, -1))) as w
from temp
) as t1
join
/* Joined on rows with status=2 using assigned weights */
(
select score, status, no, if(score=1, 2, if(status=2, 1, -2)) as w
from temp
where status=2
) as t
on t.w=t1.w
join
/* Joined on rows with greatest No. for rows with status=2 */
(
select max(no) as maxNo
from temp
where status=2
) as t2
on t.no=t2.maxNo
) limit 1 /* </joins> (Only returns one row with max No.) */
) /* </query> */
您可以从此查询中获取第一个结果:
-- If there is no row with Score 1, then the first SELECT will return nothing.
-- If there is a row with Score 1, then you will get it.
-- No row with Score 1. If there is one row with Status 2, then you will get it.
-- No row with Score 1. If there is more than one row with Status 2, then you will get the one with the highest No.
SELECT 1 AS ColumnOrder, Table.*
FROM Table
WHERE Score = 1
LIMIT 1
UNION ALL
SELECT 2 AS ColumnOrder, Table.*
FROM Table
WHERE Status=2
ORDER BY ColumnOrder, No DESC
LIMIT 1
这是我的另一个解决方案,但我将此解决方案作为另一个答案发布,因为它使用完全不同的技术(WHERE
子句中的条件条件(而不是使用 UNION
.
这避免了必须使用UNION
运算符,这可能是您所追求的(即使从技术上讲,使用两个语句UNION
算作"一个"查询(。另一个优点是它与DBMS无关,不依赖于ORDER BY X
/LIMIT 1
这主要是一种基于MySQL的技术来检索最小和最大行:
SELECT a.*
FROM tbl a
CROSS JOIN (SELECT COUNT(1) AS score1cnt FROM tbl WHERE score = 1) b
CROSS JOIN (SELECT MAX(no) AS maxno FROM tbl WHERE status = 2) c
WHERE
CASE WHEN b.score1cnt > 0 THEN a.score = 1
WHEN b.score1cnt = 0 AND c.maxno IS NOT NULL THEN a.status = 2 AND a.no = c.maxno
ELSE 0
END
你可以摆弄一个SQLFiddle演示,其中包括我的两个答案。
IF EXISTS(Select * From `table` Where score = 1)
Begin
Select * From `table` Where score = 1;
End
Else
Begin
Select *
From `table`
Where status = 2
Order by `No.` desc
LIMIT 1
End
我(还?(无法在一个查询中看到您的问题的任何优雅解决方案。
此外,我不确定您的实际问题到底是什么;您的示例代码似乎很清楚,但我发现很难推断出一个可以满足您的要求的问题
我的查询将有更多的 WHERE 子句,但在两个 条件
除此之外,我很惊讶到目前为止还没有人提议使用group by
条款。这里不讨论MySQL的语法细节,但基本上
select score, status, MAX(No) as No
from tbl
group by score, status
将为每个"组"提供一条记录,最大值为 No
.
将上述select
可以细化放入子查询中,例如
select *
from ( [*] ) as tbl
where
(score = 1)
OR (score != 1 AND status = 2)
OR /* ... more conditions of the above type... */
order by /* ... */
limit 1
([*]
代表上面的group
ed select声明(
根据需要添加或删除(order by score DESC, status DESC
和/或limit 1
子句的变体(,您应该非常接近您需要的内容。
SELECT SCORE, STATUS, NO from (
SELECT * , RANK() OVER (PARTITION BY [STATUS] ORDER BY SCORE ASC, NO DESC) AS RANK
FROM `table`
WHERE score = 1 OR [status] = 2 ) x
WHERE X.Rank = 1