如何在sql查询中替换union

xlpyo6sf 于 2021-06-18 发布在 Mysql

关注(0)|答案(4)|浏览(461)

我有一张table：attb

------------------------------------------------
Id | Name | Code | Attribute | Amount 
------------------------------------------------
1  | AV   | 123  | Alpha     | 233
------------------------------------------------
2  | TV   | 122  | Beta      | 235
------------------------------------------------
3  | TV   | 123  | Gama      | 238
------------------------------------------------
4  | CD   | 122  | Beta      | 239
------------------------------------------------
5  | TP   | 122  | Beta      | 240
------------------------------------------------
6  | RX   | 123  | Alpha     | 241
------------------------------------------------

我将其查询为：

select id,name, code, attribute, amount
from attb
where code = 123 and attribute='Alpha'
UNION
select id,name, code, attribute, amount
from attb
where code = 122;

它返回以下内容

Id | Name | Code | Attribute | Amount 
------------------------------------------------
1  | AV   | 123  | Alpha     | 233
------------------------------------------------
2  | TV   | 122  | Beta      | 235
------------------------------------------------
4  | CD   | 122  | Beta      | 239
------------------------------------------------
5  | TP   | 122  | Beta      | 240
------------------------------------------------
6  | RX   | 123  | Alpha     | 241
------------------------------------------------

有没有一种方法可以组合两个查询而不是使用联合运算符？或者更好的执行？

sql mysql oracle sybase

来源：https://stackoverflow.com/questions/53290924/how-to-replace-union-in-sql-query

4条答案

按热度按时间

6mw9ycah1#

select id,name, code, attribute, amount
from attb
where (code IN(122, 123) and attribute='Alpha') ;

赞(0）回复(0）举报 2021-06-18

nnt7mjpx2#

很容易。只是使用 or .

select id,name, code, attribute, amount
from attb
where (code = 123 and attribute='Alpha') OR code = 122

赞(0）回复(0）举报 2021-06-18

jucafojl3#

两个都放好 where 将子句合并到一个查询中：

select id,name, code, attribute, amount
from attb
where (code = 123 and attribute='Alpha') 
   or code = 122;

输出：

id  name  code  attribute  amount
1   AV    123   Alpha      233 
2   TV    122   Beta       235 
4   CD    122   Beta       239 
5   TP    122   Beta       240 
6   RX    123   Alpha      241

sqlfiddle演示

赞(0）回复(0）举报 2021-06-18

a9wyjsp74#

试试这个。

select id,name, code, attribute, amount
    from attb
    where ((code = 123 and attribute='Alpha') or (code = 122))

赞(0）回复(0）举报 2021-06-18

我来回答

如何在sql查询中替换union

4条答案

相关问题

热门标签

最新问答