# SQL语句优化,该如何处理

www.myexceptions.net  网友分享于：2013-02-13  浏览：7次
SQL语句优化
SELECT COUNT (1)
FROM big_house
WHERE lsh_xm = 100000303 AND ywlx = 0
UNION ALL
SELECT COUNT (1)
FROM big_house
WHERE lsh_xm = 100000303 AND ywlx = 2
UNION ALL
SELECT COUNT (1)
FROM big_house
WHERE lsh_xm = 100000303 AND ywlx = 3
UNION ALL
SELECT COUNT (1)
FROM big_house
WHERE lsh_xm = 100000303 AND ywlx = 4;

------解决方案--------------------
select count(ywlx), ywlx
from big_house
where
lsh_xm = 100000303 AND ywlx in(0,2,3,4)
group by ywlx

select count(ywlx)
from big_house
where
lsh_xm = 100000303 AND ywlx in(0,2,3,4)

------解决方案--------------------
SQL code
```SELECT COUNT (1)
FROM big_house
WHERE lsh_xm = 100000303 AND ywlx IN( 0,2,3,4)
GROUP BY ywlx
ORDER BY ywlx
------解决方案--------------------SELECT COUNT (1) FROM big_houseWHERE lsh_xm = 100000303  and ywlx in(0,2,3,4)
------解决方案--------------------探讨SQL codeSELECT COUNT (1)  FROM big_house WHERE lsh_xm = 100000303 AND ywlx IN( 0,2,3,4) GROUP BY ywlxORDER BY ywlx
------解决方案--------------------SQL code
SELECT SUM(CASE WHEN ywlx=0 THEN 1 ELSE 0 ) 0,
SUM(CASE WHEN ywlx=2 THEN 1 ELSE 0 ) 2,
SUM(CASE WHEN ywlx=3 THEN 1 ELSE 0 ) 3,
SUM(CASE WHEN ywlx=4 THEN 1 ELSE 0 ) 4
FROM big_house
WHERE lsh_xm = 100000303
AND ywlx IN( 0,2,3,4)
------解决方案--------------------SELECT SUM(CASE WHEN ywlx=0 THEN 1 ELSE 0 ) 0,      SUM(CASE WHEN ywlx=2 THEN 1 ELSE 0 ) 2,      SUM(CASE WHEN ywlx=3 THEN 1 ELSE 0 ) 3,      SUM(CASE WHEN ywlx=4 THEN 1 ELSE 0 ) 4   FROM big_house  WHERE lsh_xm = 100000303    AND ywlx IN( 0,2,3,4)这个才是正确的
------解决方案--------------------SELECT SUM(CASE WHEN ywlx=0 THEN 1 ELSE 0 ) 0,      SUM(CASE WHEN ywlx=2 THEN 1 ELSE 0 ) 2,      SUM(CASE WHEN ywlx=3 THEN 1 ELSE 0 ) 3,      SUM(CASE WHEN ywlx=4 THEN 1 ELSE 0 ) 4   FROM big_house  WHERE lsh_xm = 100000303    AND ywlx IN( 0,2,3,4)```