查询sql server的技术泛 [英] Technical pan in query of sql server
问题描述
i有这样的表schéma
STATION_ID | SENSORNAME | TIME_TAG | VALUE
AIN SEBOU | NIVEAU | 2015-03-01 | 20
AIN SEBOU | NIVEAU | 2015-03-02 | 70
AIN SEBOU | PLUIE1hr | 2015-03-01 | 20
AIN SEBOU | PLUIE1hr | 2015- 03-02 | 10
AZZABA | NIVEAU | 2015-03-01 | 20
AZZABA | NIVEAU | 2015-03-02 | 70
AZZABA | PLUIE1hr | 2015-03-01 | 20
AZZABA | PLUIE1hr | 2015-03-02 | 10
i希望看到包含STINE_ID = AIN SEBOU和AZZABA以及
$ b的ligne $ B SENSORNAME NIVEAU和PLUIE1hr为AIN SEBOU和NIVEAU为AZZABA
i使用此qyery但我得到所有(传感器名称)
< pre lang =sql> 选择 *
来自 XC_DATA1
其中(STATION_ID = ' AIN SEBOU' 或 STATION_ ID = ' AZZABA')
和(SENSORNAME = ' NIVEAU' 或 SENSORNAME = ' PLUIE1HR')
但是我想要这样吗
AIN SEBOU | NIVEAU | 2015-03-01 | 20
AIN SEBOU | NIVEAU | 2015-03-02 | 70
AIN SEBOU | PLUIE1hr | 2015-03-01 | 20
AIN SEBOU | PLUIE1hr | 2015-03-02 | 10
AZZABA | NIVEAU | 2015-03-01 | 20
AZZABA | NIVEAU | 2015-03-02 | 70
表格的主键是STATION_ID,SENSORNAME和TIME_TAG
打开你的WHERE子句...
STATION_ID = ' AIN SEBOU' AND SENSORNAME = ' NIVEAU')
或
(STATION_ID = ' AIN SEBOU' AND SENSORNAME = ' PLUIE1HR')
OR
(STATION_ID = ' AZZABA' AND SENSORNAME = ' NIVEAU')
OR
(STATION_ID = ' AZZABA' AND SENSORNAME = ' PLUIE1HR')
最后一行实际上不是你要求的......
所以你需要的是......
<前郎= SQL> STATION_ID = ' AIN SEBOU' AND SENSORNAME = ' NIVEAU')
OR
(STATION_ID = ' AIN SEBOU' AND SENSORNAME = ' PLUIE1HR')
OR
(STATION_ID = ' AZZABA' AND SENSORNAME = ' NIVEAU')
缩短
(STATION_ID = ' AIN SEBOU' AND (SENSORNAME = '< /跨度> <跨度class =code-string> NIVEAU' 或 SENSORNAME = ' PLUIE1HR')
OR
(STATION_ID = ' AZZABA' AND SENSORNAME = ' NIVEAU')
只是为了抛出另一个选项,我经常喜欢使用IN
因为它使查询更加简化,并且通常不需要在OR部分附加括号。
In换句话来修改Kornfeld Eliyahu Peter的好答案你可以使用类似
SELECT a.STATION_ID ,
a.SENSORNAME,
a.TIME_TAG,
a.VALUE
FROM XC_DATA1 a
< span class =code-关键字> WHERE (STATION_ID = ' AIN SEBOU' AND SENSORNAME IN (' NIVEAU',' PLUIE1HR'))
OR (STATION_ID = ' AZZABA' AND SENSORNAME IN (' NIVEAU'))
后者IN也可能是平等但我使用IN来保持比较相似。
请参阅 IN(Transact-SQL) [ ^ ]
hi,
i have table like this schéma
STATION_ID|SENSORNAME|TIME_TAG |VALUE
AIN SEBOU |NIVEAU |2015-03-01|20
AIN SEBOU |NIVEAU |2015-03-02|70
AIN SEBOU |PLUIE1hr |2015-03-01|20
AIN SEBOU |PLUIE1hr |2015-03-02|10
AZZABA |NIVEAU |2015-03-01|20
AZZABA |NIVEAU |2015-03-02|70
AZZABA |PLUIE1hr |2015-03-01|20
AZZABA |PLUIE1hr |2015-03-02|10
i want to seelect ligne wich containe STATION_ID = AIN SEBOU and AZZABA and for
SENSORNAME NIVEAU and PLUIE1hr for AIN SEBOU and NIVEAU for AZZABA
i have use this qyery but i get all (sensor name)
select *
from XC_DATA1
where (STATION_ID='AIN SEBOU'or STATION_ID= 'AZZABA')
and (SENSORNAME='NIVEAU' OR SENSORNAME='PLUIE1HR' )
but me i want rslt like this
AIN SEBOU |NIVEAU |2015-03-01|20
AIN SEBOU |NIVEAU |2015-03-02|70
AIN SEBOU |PLUIE1hr |2015-03-01|20
AIN SEBOU |PLUIE1hr |2015-03-02|10
AZZABA |NIVEAU |2015-03-01|20
AZZABA |NIVEAU |2015-03-02|70
for primary key of my table are STATION_ID,SENSORNAME and TIME_TAG
To open up you WHERE clause...
STATION_ID = 'AIN SEBOU' AND SENSORNAME = 'NIVEAU') OR (STATION_ID = 'AIN SEBOU' AND SENSORNAME = 'PLUIE1HR') OR (STATION_ID = 'AZZABA' AND SENSORNAME = 'NIVEAU') OR (STATION_ID = 'AZZABA' AND SENSORNAME = 'PLUIE1HR')
Where the last line actually not as you requested...
So what you need is...
STATION_ID = 'AIN SEBOU' AND SENSORNAME = 'NIVEAU') OR (STATION_ID = 'AIN SEBOU' AND SENSORNAME = 'PLUIE1HR') OR (STATION_ID = 'AZZABA' AND SENSORNAME = 'NIVEAU')
To make it short
(STATION_ID = 'AIN SEBOU' AND (SENSORNAME = 'NIVEAU' OR SENSORNAME = 'PLUIE1HR') OR (STATION_ID = 'AZZABA' AND SENSORNAME = 'NIVEAU')
Just to throw another option, I often like to useIN
since it makes the query a bit more simplified and often removes the need for additional parenthesis around OR section.
In other words to modify the good answer from Kornfeld Eliyahu Peter you can use something like
SELECT a.STATION_ID, a.SENSORNAME, a.TIME_TAG, a.VALUE FROM XC_DATA1 a WHERE (STATION_ID = 'AIN SEBOU' AND SENSORNAME IN ('NIVEAU', 'PLUIE1HR')) OR (STATION_ID = 'AZZABA' AND SENSORNAME IN ('NIVEAU'))
The latter IN could also be equality but I used IN to keep the comparisons similar.
See IN (Transact-SQL)[^]
这篇关于查询sql server的技术泛的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!