SQL – select

データ取り出し例

select * from table ; 

重複なしのデータ取り出し例

SELECT distinct Sousa FROM GVIS_log order by Sousa ;

内部結合

内部結合（INNER JOIN）とは、結合する両方のテーブルどちらにも同じキーが存在するレコードのみ残し、それ以外は切り捨てる

外部結合

２つのテーブルを結合するのは内部結合と同じだが、どちらか片方のテーブルにデータがあればレコードが取得

左外部結合

LEFT OUTER JOIN（左外部結合）とは、FROM句に結合するテーブルを書く時、左側に書いた方をメインテーブルにする外部結合。

その結果、左側のメインテーブルに存在するレコードは、仮に右側のテーブルに同じキーのレコードが無くても全て取得されるが、反対に右側のテーブルだけにしか存在しないレコードは取得されない。

例）顧客マスタから顧客番号で顧客社名と担当者名を得る。

select 
    GVIS_mst_customer.CompanyName1,
    GVIS_mst_customer.CompanyPersonName1,
    GVIS_transaction.* 
from GVIS_transaction
LEFT OUTER JOIN GVIS_mst_customer 
ON GVIS_mst_customer.GVIS_CustNo = GVIS_transaction.OrderCustNo
order by OrderNo desc
;

サブクエリ・union

select * from 
(
    select
    '2Keihi' as Koumoku,
    year(workPeriod) as year,month(workPeriod) as month,
    sum(Kng) as Kng
    from (
        select  * , 
            concat(
                lpad(GVIS_mst_kamoku.kamokuNo,3,'0'),'-',GVIS_mst_kamoku.kamokuName
            ) as concatKamoku 
        from GVIS_keihi
        LEFT JOIN GVIS_mst_kamoku ON GVIS_keihi.Kamoku = 
            concat(
                lpad(GVIS_mst_kamoku.kamokuNo,3,'0'),'-', GVIS_mst_kamoku.kamokuName
            )
        where keihiTaisho = 1
    ) as P1
    group by year(workPeriod) ,month(workPeriod)

    union

    select
        '1Uriage' as Koumoku,
        year(workPeriod) as year,month(workPeriod) as month,
        sum(Kng) as Kng
    from (
        select * from GVIS_keihi
        where year(workPeriod) = $arg1
            and Kng <> 0  
            and (Kamoku = '101-現金売上' or Kamoku = '102-預金売上')
    ) as P2
    group by year(workPeriod) ,month(workPeriod)
) as P3
order by year,month,Koumoku
;

group by/having

select year,month, 
    ceil(COALESCE(Worktime,0)) as Worktime,
    ceil(COALESCE(Choku_worktime,0)) as Choku_worktime,
    ceil(COALESCE(Uriage,0)) as Uriage,
    ceil(COALESCE((Uriage / Choku_worktime),0)) as Tanka,
    ceil(COALESCE(Other_worktime)) as Other_worktime
from (
    select 
        year(workPeriod) as year ,
        month(workPeriod) as month ,
        sum(Worktime) as Worktime ,
        ( select sum(Worktime) from GVIS_work
            where year(workPeriod) = year 
              and month(workPeriod) = month
              and Kng <> 0
        ) as Choku_worktime,
        ( select sum(Kng) from GVIS_work
            where year(workPeriod) = year 
              and month(workPeriod) = month 
              and Kng<> 0
        ) as Uriage,
        ( select sum(Worktime) from GVIS_work
            where year(workPeriod) = year 
              and month(workPeriod) = month 
              and (Kng = 0 or isnull(Kng))
        ) as Other_worktime
    from GVIS_work
    group by year(workPeriod),month(workPeriod)
    having year = $arg1
    order by year,month
 )
 as Shukei
;

日付条件で抽出

select sum(Kng) as Kng from (
  select * from GVIS_keihi
  where substr(Kamoku,5) = '預金売上'
    and date(`Keihi_date`) >= $arg1 "-" $arg2 "-" $arg3
    and date(`Keihi_date`) <  $arg4 "-" $arg5 "-1"
) as p1
;

ランキング