MySQL數據庫中分組統計的問題

首先準備四張表A、B、C、D，

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

A　???? |　B　 |　C　? |　D

a?? b?? |?? a?? c?? |?? a?? d?? |?? d?? e

1?? 1?? |?? 1?? 1?? |?? 1?? A?? |?? A?? 1

2?? 2?? |?? 1?? 3?? |?? 2?? B?? |?? B?? 2

3?? 4?? |?? 1?? 4?? |?? 3?? C?? |?? C?? 3

4?? 6?? |?? 2?? 1?? |?? 4?? D?? |?? D?? 4

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

下面我們需要用一條sql語句將A表所有的列，B表對與A表a字段相關聯的列求count,通過C表,將D表與A表關聯起來，得到的結果如下：

1?? 1?? 3?? 1

2?? 2?? 1?? 2

3?? 4?? 0?? 3

4?? 6?? 0?? 4

Mysql語句解決：

select A.a,A.b,IFNULL(c,0) as c,D.a

from (select 1 as a,2 as b union all

select 2 as a,2 as b union all

select 3 as a,4 as b union all

select 4 as a,6 as b) as A

left outer join

(select a,count(a) as c from(

select 1 as a,3 as c union all

select 1 as a,3 as c union all

select 1 as a,3 as c union all

select 2 as a,3 as c ) B group by a) as B

on A.a=B.a

join

(select 1 as a,'A' as d union all

select 2 as a,'B' as d union all

select 3 as a,'C' as d union all

select 4 as a,'D' as d ) as C

on A.a=C.a

join(select 1 as a,'A' as d union all

select 2 as a,'B' as d union all

select 3 as a,'C' as d union all

select 4 as a,'D' as d ) as D

on D.d=C.d

本文作者:相關閱讀:

ASP3.0高級編程(二)

jQuery實現原理的模擬代碼 -6 代碼下載

兩難的選擇:充滿Trick的CSS

讓IE6、IE7、IE8支持CSS3的腳本

Microsoft SQL Server 2008 Express版本

JSP由淺入深(6)—— JSP聲明

Linux下磁盤設備文件(sda,sdb,sdc….)變化問題

日期 時間js控件

保存CSS中的圖片的幾種方法

Sun&MySQL全球巡展中國站正式啟動

簡單的ASP分頁代碼(測試正確)

PHP中通過語義URL防止網站被攻擊的方法分享

聊天室實現私聊(三)

AMD顯卡最新Linux系統版本驅動大幅更新(圖)

總結

以上是生活随笔為你收集整理的c mysql 统计不重复数据库,MySQL_MySQL数据库中分组统计的问题，首先准备四张表A、B、C、D， - phpStudy...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。