mysql 之 SQL 语句复习

mysql 之 SQL 语句复习

查询字段名为“zone_staff_name” 且值为 “吕延旭” 的数据有多少条？

方法 1：

SELECT 
    zone_staff_name,
    COUNT(zone_staff_id) 
FROM 
    base_dorm_sal 
GROUP BY 
    zone_staff_name
HAVING 
    zone_staff_name='吕延旭'

思路：对 zone_staff_name 先分组，分组以后用 having 关键字筛选，然后计数。

方法 2：不分组直接筛选，然后使用聚合函数

SELECT
    zone_staff_name,
    COUNT(zone_staff_id)
FROM
    base_dorm_sal
WHERE 
    zone_staff_name='吕延旭'

SELECT '合计' province_desc,'-' prov_staff_name,'-' city_desc,'-' city_staff_name,
zone_staff_name,0 AS site_id,'--' site_desc,week_sal_order_zone,month_sal_order_zone,
COUNT(DISTINCT CASE WHEN tid>0 THEN  tid END ) 战队数
FROM base_dorm_sal 
WHERE order_date=20150630 AND zone_staff_id =377 
GROUP BY 
zone_staff_name,week_sal_order_zone,month_sal_order_zone

SELECT COUNT(DISTINCT tid)
FROM base_dorm_sal 
WHERE order_date=20150630 AND zone_staff_id =377 AND tid>0

SELECT COUNT(tid)
FROM base_dorm_sal 
WHERE order_date=20150630 AND zone_staff_id =377 AND tid>0