sql例子

orcal

事例一

SELECT
	TBL. ID,
	TD.REGIONALID,
	TD.REGIONALNAME AS REGIONALNAME,
	TD.MACNAME AS MACNAME,
	TD.DEALERNAME AS DEALERNAME,
/**
IF (
	TBL.SERIESID <> 0,
	TBL.SERIESID,
	NULL
) AS VEHICLESERIESID,
**/

(
	CASE WHEN TBL.SERIESID <> 0  THEN TBL.SERIESID  ELSE  NULL END
) AS wqk,


 (
	CASE
	WHEN TBL.SERIESID <> 0 THEN
		(
			SELECT
				SERIESNAME
			FROM
				T_VEHICLE_SERIES
			WHERE
				ID = TBL.SERIESID
		)
	END
) AS VEHICLESERIESNAME,

NVL(TBL.SPECID   , NULL) AS VEHICLETYPEID,
"NVL2"(TBL.SPECID  , TBL.SPECID, NULL)AS VEHICLETYPEID1,
/**
IF ( TBL.SPECID <> 0, TBL.SPECID, NULL) AS VEHICLETYPEID,
**/
 (
	CASE
	WHEN TBL.SPECID <> 0 THEN
		(
			SELECT
				TYPENAME
			FROM
				T_VEHICLE_TYPE
			WHERE
				ID = TBL.SPECID
		)
	END
) AS VEHICLETYPENAME,
 TCP.PROVINCEID,
 TBL.CREATETIME AS CHANNELTIME,
 TBL.LEADSTYPE AS ISEXP
FROM
	T_PCAUTO_LEADS TBL
LEFT JOIN (
	SELECT
		DEALERCODE,
		ID AS DEALERID,
		DEALERNAME,
		MACID AS MACID,
		MACNAME,
		REGIONALID AS REGIONALID,
		REGIONALNAME
	FROM
		T_DEALER
) TD ON TD.DEALERCODE = TBL.DEALERCODE
LEFT JOIN (
	SELECT
		TC. ID AS CITYID,
		TC. NAME AS CITYNAME,
		TP. ID AS PROVINCEID,
		TP. NAME AS PROVINCENAME
	FROM
		T_CITY TC
	INNER JOIN T_PROVINCE TP ON TP. ID = TC.PROVINCEID
) TCP ON TCP.CITYID = TBL.CITYID
AND TCP.PROVINCEID = TBL.PROVINCEID

2行转列

mysql> SELECT YEAR(open_date) year, COUNT(*) how_many
         FROM account
         WHERE open_date > '1999-12-31'
         GROUP BY YEAR(open_date);

    +------+----------+
    | year | how_many |
    +------+----------+
    | 2000 |        3 |
    | 2001 |        4 |
    | 2002 |        5 |
    | 2003 |        3 |
    | 2004 |        9 |
    +------+----------+

mysql> SELECT
           SUM(CASE
                 WHEN EXTRACT(YEAR FROM open_date) = 2000 THEN 1
                 ELSE 0
               END) year_2000,
           SUM(CASE
                 WHEN EXTRACT(YEAR FROM open_date) = 2001 THEN 1
                 ELSE 0
               END) year_2001,
           SUM(CASE
                 WHEN EXTRACT(YEAR FROM open_date) = 2002 THEN 1
                 ELSE 0
               END) year_2002,
           SUM(CASE
                 WHEN EXTRACT(YEAR FROM open_date) = 2003 THEN 1
                 ELSE 0
               END) year_2003,
           SUM(CASE
                 WHEN EXTRACT(YEAR FROM open_date) = 2004 THEN 1
                 ELSE 0
               END) year_2004,
           SUM(CASE
                 WHEN EXTRACT(YEAR FROM open_date) = 2005 THEN 1
                 ELSE 0
               END) year_2005
         FROM account
         WHERE open_date > '1999-12-31';

 +-----------+-----------+-----------+-----------+-----------+-----------+
    | year_2000 | year_2001 | year_2002 | year_2003 | year_2004 | year_2005 |
    +-----------+-----------+-----------+-----------+-----------+-----------+
    |         3          |         4         |         5           |         3          |         9          |         0       |
    +-----------+-----------+-----------+-----------+-----------+-----------+

MYSQL  添加字段和注释

-- Mysql添加字段和注释
ALTER TABLE t_administrator ADD IMGPATH VARCHAR (300) NULL;
/*加注释的*/
ALTER table t_administrator MODIFY IMGPATH varchar(300) COMMENT '头像（图片地址）';