SQLITE有条件的多表联接 [英] SQLITE multiple table join with a condition

问题描述

我有这些表:

doodhiya

dhid INTEGER PRIMARY KEY NOT NULL, 
dname TEXT NOT NULL, 
dfname TEXT NOT NULL, 
dage INTEGER NOT NULL, 
dadd TEXT, 
dphone INTEGER NOT NULL, 
demail TEXT NOT NULL

doodhdata

doodhdata

dtid INTEGER PRIMARY KEY NOT NULL, 
ddate INTEGER NOT NULL, 
dmonth INTEGER NOT NULL,
dyear INTEGER NOT NULL, 
dmilk INTEGER NOT NULL, 
dprice INTEGER NOT NULL default 35 , 
dmore TEXT, 
ddhid INTEGER NOT NULL

pricemilk

pricemilk

pid INTEGER PRIMARY KEY NOT NULL, 
pmonth INTEGER NOT NULL, 
pyear INTEGER NOT NULL, 
milkprice INTEGER NOT NULL, 
typeperson TEXT, 
userid INTEGER, 
gheeprice INTEGER, 
defaultprice TEXT

现金数据

cashid INTEGER PRIMARY KEY NOT NULL,
cashdate INTEGER NOT NULL, 
cashmonth INTEGER NOT NULL, 
cashyear INTEGER NOT NULL, 
cashamount INTEGER NOT NULL, 
uid INTEGER NOT NULL, 
utype TEXT NOT NULL, 
cashtype TEXT NOT NULL, 
cashmore TEXT

我想制作每月帐单，我可以用它成功购买...但是在帐单中，我如何显示上个月的余额....我正在尝试使用它

I want to make a monthly bill and I am succeed buy using it... but in a bill how can i show last month balance....I am trying to use it

 SELECT
  ddhid, dmonth, dyear, dmilk,
  userid, pmonth, pyear, milkprice, 
  uid, cashmonth, cashyear, cashamount, utype, 
  SUM(dmilk) AS totalmilk,  
  SUM(dmilk*milkprice) AS totalamount, 
  SUM(cashamount) AS totalcash
  FROM 
 doodhdata 
 LEFT JOIN pricemilk ON (
   doodhdata.ddhid = pricemilk.userid 
   AND doodhdata.dmonth = pricemilk.pmonth 
   AND doodhdata.dyear = pricemilk.pyear
 )
 LEFT JOIN cashdata ON (
   doodhdata.ddhid = cashdata.uid 
   AND doodhdata.dmonth = cashdata.cashmonth 
   AND doodhdata.dyear = cashdata.cashyear
 )
 WHERE 
   dmonth > '$mikdatem' 
   AND dyear='$mikdatey' 
   AND ddhid='$dhid'

但是当milkprice为NULL时，我想使用defaultprice ....怎么可能...?

But I want to use defaultprice when milkprice is NULL....how it is possible...?

推荐答案

使用

COALESCE(milkprice, defaultprice)

代替查询中的milkprice.

请参见 SQLite核心函数文档.

这篇关于SQLITE有条件的多表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！