SQL连接多个ID逗号分隔 [英] SQL Join multiple ids comma separated

问题描述

我有两个表，成本和用户。

i have two tables, costs and users.

成本表：

id | project | category | type | description | status | value | date_reg | date_edit | edited | id_user | name

现在我需要，将用户ID保存到费用表中的id_user上，我该怎么做？我另存为1 | 2 | 3或1,2,3吗？但是我需要加入表用户才能获取一些信息，例如姓名，电子邮件和其他内容。

Now i need, save id from users on id_user in costs table, how i do it? i save as 1|2|3 or 1,2,3 ? But i need join table users for get some infos, like name, email and others things.

费用表：
id_user
1 | 2 | 3 | 4或1,2,3,4或其他任何内容。

Costs table: id_user 1|2|3|4 or 1,2,3,4 or anything.

用户表：
id |名称|密码|电邮| date_reg |级别|照片

User table: id | name | password | email | date_reg | level | photo

因为同一项目上可能有很多用户（表成本）。

Because may have many users on the same project(table costs).

我要在费用表上按id_users列出用户（从用户表中获得）信息。
我正在使用codeigniter。

I want list users (from user table) infos by id_users on costs table. I am using codeigniter.

推荐答案

您需要为每个费用添加一个新的费用表：

You need a new costs table to each cost:

Projects
-----------------
Project_id
Name

Users
--------------------
User_id
Username

Costs
----------------
Cost_id
Amount
Project_id
User_id

..

要查找每个用户每个项目的项目成本，请执行以下操作：

To find projects costs per project per user:

Select sum(c.Amount) as total_cost, count(*) as num_costs, p.Project_id,p.Name as project_name, u.user_id, u.Username
From costs c inner join project p on c.Project_id=c.Project_id
Inner join users u
On c.User_id=u.User_id
Group by p.Project_Id, u.user_id

每个用户的费用

Select sum(c.Amount) as total_cost, count(*) as num_costs, u.user_id, u.Username
From costs c 
Inner join users u
On c.User_id=u.User_id
Group by u.user_id

这篇关于SQL连接多个ID逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！