sql-test

//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

 

---------------