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