MySql 积累

在MySql中给记录添加行号 

select z.*,@rowno:=@rowno+1 as rowno from zt_testresult z, (select @rowno:=0) t ORDER BY `case`

sql表连接

内连接：通过运算符连接（=，<,>等）。

外连接：分为左外连接，右外连接，全连接。

左外连接（LEFT JOIN / LEFT OUTER JOIN）：连接的结果集包括LEFT  OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

右外连接（RIGHT JOIN / RIGHT OUTER JOIN）：右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

全连接（FULL JOIN / FULL OUTER JOIN）：返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。  

交叉连接：笛卡尔积。

例如：

SELECT CONCAT_WS('<<',c.title,m1.`name`,m2.`name`,m3.`name`,m4.`name`,m5.`name`,m6.`name`,p.`name`)
FROM zt_case c
LEFT JOIN zt_product p ON c.product = p.id
LEFT JOIN zt_module m1 ON c.module = m1.id
LEFT JOIN zt_module m2 ON m1.parent = m2.id
LEFT JOIN zt_module m3 ON m2.parent = m3.id
LEFT JOIN zt_module m4 ON m3.parent = m4.id
LEFT JOIN zt_module m5 ON m4.parent = m5.id
LEFT JOIN zt_module m6 ON m5.parent = m6.id

SELECT * FROM
((
SELECT DISTINCT t1.id AS num,'需求' AS type,t2.`name` AS product,t1.title,
(CASE t3.assignedTo WHEN 'closed' THEN t3.lastEditedBy ELSE t3.assignedTo END) AS devloper,
t1.`status`,t4.`name` AS version,t1.openedBy,t1.pri,t1.openedDate  
FROM zt_story t1
LEFT JOIN zt_product t2 ON t2.id=t1.product 
LEFT JOIN zt_task t3 ON t1.id= t3.story AND t3.type='devel'
LEFT JOIN zt_build t4 ON FIND_IN_SET(t1.id,t4.stories) 
WHERE t1.deleted='0' AND t2.deleted='0' AND t3.deleted='0' AND t4.deleted='0'
)
UNION ALL
(
SELECT DISTINCT t5.id AS num,'问题' AS type,t6.`name` AS product,t5.title AS title,
(CASE t5.assignedTo WHEN 'closed' THEN t5.resolvedBy ELSE t5.assignedTo END) AS developer,
t5.`status` AS `status`,t7.`name` AS version,t5.openedBy AS openedBy,t5.pri AS pri,t5.openedDate AS openedDate
FROM zt_bug t5
LEFT JOIN zt_product t6 ON t6.id=t5.product
LEFT JOIN zt_build t7 ON FIND_IN_SET(t5.id,t7.bugs)
WHERE t5.deleted='0' AND t6.deleted='0' AND t7.deleted='0' 
) ORDER BY product
) TT
WHERE 1=1

获得字段中不重复记录数目

COUNT(DISTINCT 字段)