Select groups. Case ForRun?
here is a structure :-/ alt text http://s004.radikal.ru/i207/1002/79/ed7ac0a97d0b.jpg So I need to select ID and Names from CfgListGroupParIzm for CfgIzmeritel using Type and where ForRun - False it's
SELECT A.ID_ListGroupParIzm, A.Name
FROM CfgListGroupParIzm A, CfgIzmeritel B
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit
AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=0
AND ID and NamePoint(from CfgIzmerPoint)+Name from CfgListGroupParIzm from same CfgIzmeritel where ID_Izmerit from CfgIzmeritel =ID_Izmerit from CfgIzmerPoint and ForRun from C开发者_如何学PythonfgListGroupParIzm - True
so finally I need something like
SELECT A.ID_ListGroupParIzm, A.Name
FROM CfgListGroupParIzm A, CfgIzmeritel B
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit
AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=0
UNION -- ??
SELECT A.ID_ListGroupParIzm, (C.Name + A.Name) AS Name
FROM CfgListGroupParIzm A, CfgIzmeritel B, CfgIzmerPoint C
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit
AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=1
AND C.ID_Izmerit=@ID_Izmerit
I think I need use some other construction without Union because I make mostly the same in both selects :-/
You could use a UNION ALL
SELECT A.ID_ListGroupParIzm, A.Name
FROM CfgListGroupParIzm A,
CfgIzmeritel B
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit
AND B.ID_Izmerit=@ID_Izmerit
AND A.ForRun=0
UNION ALL
SELECT A.ID_ListGroupParIzm,
(C.Name + A.Name) AS Name
FROM CfgListGroupParIzm A, CfgIzmeritel B, CfgIzmerPoint C
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit
AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=1
AND C.ID_Izmerit=@ID_Izmerit
UNION ALL will bring back all the results, from both queries. If you require the distinct list of this, without any duplicates, use UNION.
Have a look at Union vs. Union All
and SQL UNION Operator
Please also remember that using aliases other than A,B,C,etc is more appropriate. Try using slightly more descriptive aliases that will make it easier to follow in larger queries.
If my assumption is correct that CfgIzmeritel and CfgIzmerPoint are one to one you could try this using
SELECT grp.ID_ListGroupParIzm,
CASE
WHEN ForRun=1 AND pnt.ID_TypeIzmerit IS NOT NULL THEN (C.Name + A.Name)
ELSE grp.Name
END AS Name
FROM CfgListGroupParIzm grp INNER JOIN
CfgIzmeritel item ON grp.ID_TypeIzmerit = item.ID_TypeIzmerit LEFT JOIN
CfgIzmerPoint pnt ON grp.ID_TypeIzmerit = pnt.ID_TypeIzmerit
WHERE item.ID_Izmerit=@ID_Izmerit
From the where clause you should be able to ignore the ForRun, as this will only be used in the CASE statement.
I stile think that for readibility you should use the UNION/UNION ALL and only try to create a single query if the performance is bad (after you had a look at indexes).
精彩评论