我正在使用sqlite3数据库,我试图从两个交叉连接的表中获取数据。它们有一些外键,我无法获得货币名称的参考值(b_currency和s_currency)。
他们需要像“美元”,“欧元”,“TRL”等。
SQL语句:
select
a.pid,
person.fullname,
a.amount as b_amount,
b.amount as s_amount,
a.currency as b_currency,
b.currency as s_currency,
a.rate as b_rate,
b.rate as s_rate,
`user`.username,
a.`date`
from buy_process as a
inner join person
on a.fullname=person.id
inner join currency
on b_currency=currency.id and s_currency=currency.id
inner join `user`
on a.`user`=`user`.id
cross join sell_process as b
where a.pid=b.pid;
Buy_Process和Sell_Process表字段相同:
-- Describe BUY_PROCESS
CREATE TABLE `buy_process`(
id integer primary key AUTOINCREMENT,
pid VARCHAR(50) NOT NULL UNIQUE,
fullname INTEGER NOT NULL,
amount VARCHAR(50) NOT NULL,
currency INTEGER NOT NULL,
rate VARCHAR(50) NOT NULL,
`user` INTEGER NOT NULL,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fkbuy_fullname FOREIGN KEY(fullname) REFERENCES person(id),
CONSTRAINT fkbuy_currency FOREIGN KEY(currency) REFERENCES currency(id),
CONSTRAINT fkbuy_user FOREIGN KEY(`user`) REFERENCES `user`(id)
);
结果:
Result image
我尝试更改字段名称,但未成功:
a.pid,
person.fullname,
a.amount as b_amount,
b.amount,
currency.name as b_currency,
currency.name as s_currency,
a.rate as b_rate,
b.rate as s_rate,
`user`.username,
a.`date`
from buy_process as a
inner join person
on a.fullname=person.id
inner join currency
on b_currency=currency.id and s_currency=currency.id
inner join `user`
on a.`user`=`user`.id
cross join sell_process as b
where a.pid=b.pid;
1条答案
按热度按时间f45qwnt81#
我不明白你想用交叉连接达到什么目的(你有一个条件a.pid = b.pid,为什么不直接内部连接它们呢?)
您需要联接货币表两次,一次联接买入货币,一次联接卖出货币: