//sql语句请教查询结果相加,表别名。
sql描述:同一张table里面,某一字段值确定相同,另一字段值确定的情况下。
SELECT COUNT(*) AS TOTAL,CM_WGT
FROM HZW_CM
WHERE CM_WGT=10 AND PRODUCT_CTG IN('BB煲','DHA')
--
SELECT NAMES,SUM(sale) AS sale
FROM
(SELECT NAMES, sale FROM db1
UNION
SELECT NAMES, sale FROM db2
)
GROUP BY NAMES
排序,取值:
SELECT
COUNT(*) AS TOTALNUM
FROM HZW_OV
WHERE PRODUCT_NAME LIKE '%45%'
ORDER BY PRODUCT_ID DESC;
--
SELECT
SUM(CASE WHEN consumer_bhv='B' THEN 1 ELSE 0 END) AS B_num
FROM dba.transactions;
--Test result;
select top 50 * from hzw_cm;
-- 求出 表REC_1_PRDCT_INFO 中各个user_id下的各个ctg中价格最高的商品集
SELECT
a.user_id,
a.product_ctg,
a.max_price,
b.product_name,
b.product_id
FROM (SELECT
user_id,
product_ctg,
MAX(product_price) max_price
FROM REC_1_PRDCT_INFO
GROUP BY user_id,product_ctg) AS a,
REC_1_PRDCT_INFO AS b
WHERE a.user_id = b.user_id
AND a.product_ctg = b.product_ctg
AND a.max_price = b.product_price
-- DISTINCT
SELECT DISTINCT user_id
FROM REC_1_PRDCT_INFO
-- 删除表
DROP TABLE TEMP_TRANSACTIONS
-- 删除全表数据
DELETE FROM ADS_SITE_STYLE
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE Rec_date BETWEEN '2012-08-01' AND '2012-08-10'
-- mysql CONCAT 函数【连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL】
SELECT CONCAT(nickname,ads_url_join) AS users,nickname
FROM RG_USERS
-- mysql LIMIT 函数
SELECT *
FROM TRANSACTIONS
LIMIT 2,9
SELECT
user_id,
product_ctg,
product_name,
product_price
FROM REC_1_PRDCT_INFO
WHERE product_price IN(SELECT
MAX(product_price) AS product_price
FROM REC_1_PRDCT_INFO
GROUP BY user_id,product_ctg)
-- 求出 TRANSACTIONS 中各个user_id下的成单量最多的前5件商品集,包含各个商品的成单总数量
SELECT
user_id,
product_ctg,
COUNT(*)
FROM TRANSACTIONS
WHERE consumer_bhv = 'B'
GROUP BY user_id
-- 求出 表REC_1_PRDCT_INFO 中各个user_id下的各个ctg中价格最高的商品集
SELECT
a.user_id,
a.product_ctg,
a.max_price,
b.product_name,
b.product_id
FROM (SELECT
user_id,
product_ctg,
MAX(product_price) max_price
FROM REC_1_PRDCT_INFO
GROUP BY user_id,product_ctg) AS a,
REC_1_PRDCT_INFO AS b
WHERE a.user_id = b.user_id
AND a.product_ctg = b.product_ctg
AND a.max_price = b.product_price
GROUP BY a.user_id, a.product_ctg,a.max_price
-- INNER JOIN
SELECT
b.user_id,
a.nickname,
a.user_url,
b.pctg_wgt
FROM REC_1_USER_WGT AS b
INNER JOIN RG_USERS AS a
ON a.rg_client_id = b.user_id
ORDER BY user_id DESC
-- RIGHT JOIN
SELECT
a.nickname,
a.user_url,
b.pctg_wgt,
b.user_id
FROM REC_1_USER_WGT AS b
RIGHT JOIN RG_USERS AS a
ON a.rg_client_id = b.user_id
ORDER BY user_id DESC
-- LEFT JOIN
SELECT
a.nickname,
a.user_url,
b.pctg_wgt,
b.user_id
FROM REC_1_USER_WGT AS b
LEFT JOIN RG_USERS AS a
ON a.rg_client_id = b.user_id
ORDER BY user_id DESC
-- CROSS JOIN
SELECT
b.user_id,
a.nickname,
a.user_url,
b.pctg_wgt
FROM REC_1_USER_WGT AS b
CROSS JOIN RG_USERS AS a
WHERE a.rg_client_id = b.user_id
ORDER BY user_id DESC
-- UNION; UNION ALL;[UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。]
-- [默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。]
SELECT user_id FROM REC_1_USER_WGT
UNION
SELECT user_id FROM REC_1_PRDCT_INFO
-- IS NULL 和 IS NOT NULL 操作符。
SELECT *
FROM RG_USERS
WHERE ads_url_join IS NULL
SELECT *
FROM RG_USERS
WHERE ads_url_join IS NOT NULL
-- 聚合函数[AVG();COUNT(column_name) 函数返回指定列的值的数目(NULL 不计入);COUNT(*) 函数返回表中的记录数;COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目;
-- 【MAX();MIN();SUM 函数返回数值列的总数(总额);;;;】
SELECT
AVG(user_max_sub_num) AS subAverage
FROM REPORT_USERS
SELECT *
FROM REPORT_USERS
WHERE user_max_sub_num < (SELECT
AVG(user_max_sub_num) AS subAverage
FROM REPORT_USERS)
SELECT
COUNT(product_var1)
FROM REC_1_PRDCT_INFO
SELECT
COUNT(DISTINCT product_var1)
FROM REC_1_PRDCT_INFO
SELECT
COUNT(DISTINCT user_id)
FROM REC_1_PRDCT_INFO
SELECT MAX(rec_charge_rate) FROM REC_1_PRDCT_INFO
SELECT MIN(rec_charge_rate) FROM REC_1_PRDCT_INFO
SELECT SUM(rec_charge_rate) FROM REC_1_PRDCT_INFO
-- GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组
SELECT user_id,SUM(rec_charge_rate) AS num FROM REC_1_PRDCT_INFO
GROUP BY user_id
SELECT user_id,product_var1,SUM(rec_charge_rate) AS num FROM REC_1_PRDCT_INFO
GROUP BY user_id,product_var1
-- HAVING 子句[在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用]
-- [查找订单总金额少于 100 的客户。]
SELECT
consumer_id,
SUM(product_price)
FROM TRANSACTIONS
GROUP BY consumer_id
HAVING SUM(product_price) < 100
SELECT
consumer_id,
SUM(product_price) AS total
FROM TRANSACTIONS
WHERE rec_date='2012-08-01'
GROUP BY consumer_id
HAVING total BETWEEN 1 AND 100
SELECT
Customer,
SUM(OrderPrice)
FROM Orders
WHERE Customer = 'Bush'
OR Customer = 'Adams'
GROUP BY Customer
HAVING SUM(OrderPrice) > 1500
-- UCASE 函数把字段的值转换为大写;LCASE 函数把字段的值转换为小写
SELECT UCASE(user_url)
FROM RG_USERS
SELECT LCASE(user_url)
FROM RG_USERS
-- MID 函数用于从文本字段中提取字符
SELECT
MID(user_url,1,3) AS single
FROM RG_USERS
-- ROUND 函数用于把数值字段舍入为指定的小数位数。[ 四舍五入,舍入为最接近的整数]
SELECT
ROUND(product_price)
FROM REC_1_PRDCT_INFO
WHERE product_price =582.40 OR product_price =4.90 OR product_price =93.50
-- myql 时间函数
SELECT NOW()
SELECT YEAR('2003-03-31')+5 AS c_year;
SELECT
DAYNAME('2000-01-01') AS week_day;
SELECT
DAYOFYEAR('2000-12-31');
SELECT
DATE_ADD('2003-07-13', INTERVAL 14 DAY);
SELECT
'The number of CTG is :',
COUNT(*)
FROM RG_USERS
--
CREATE DATABASE rec
DROP TABLE temp_cid_mapping
SELECT
COUNT(*)
FROM rec_1_prdct_info
SELECT
COUNT(*)
FROM temp_curr_trans
-- group by; order by ..asc
SELECT
a.user_id,
COUNT(*) AS total
FROM rec_1_user_wgt a,
rec_1_prdct_info b
WHERE a.user_id = b.user_id
GROUP BY user_id
ORDER BY total DESC
-- INNER JOIN..ON; WHERE; group by; order by ..asc
SELECT
a.user_id,
COUNT(*) AS total
FROM (rec_1_user_wgt AS a
INNER JOIN rec_1_prdct_info AS b
ON a.user_id = b.user_id)
INNER JOIN temp_curr_trans AS c
ON b.product_url = c.session_url
WHERE b.product_var1 IS NOT NULL
GROUP BY user_id
ORDER BY total DESC
-- 两张表联合join
-- INNER JOIN; USING; group by; order by ..asc
SELECT
user_id ,COUNT(*) AS total,CONCAT(user_id,product_url)
FROM rec_1_user_wgt b
INNER JOIN rec_1_prdct_info a
USING (user_id)
GROUP BY user_id
ORDER BY total DESC
SELECT DISTINCT user_id FROM temp_curr_trans
-- 三张表联合join
SELECT
a.user_id,
COUNT(*) AS total
FROM (temp_curr_trans AS c
INNER JOIN rec_1_prdct_info AS b
ON b.product_url = c.session_url)
INNER JOIN rec_1_user_wgt AS a
ON a.user_id = b.user_id
WHERE b.product_var1 IS NOT NULL
GROUP BY user_id
ORDER BY total DESC
-- 两张表联合left outer join on
-- INNER JOIN; USING; group by; order by ..asc
SELECT
user_id ,COUNT(*) AS total,CONCAT(user_id,product_url)
FROM rec_1_user_wgt b
LEFT OUTER JOIN rec_1_prdct_info a
USING (user_id)
GROUP BY user_id
ORDER BY total DESC
---------------