搜索withina逗号分隔列表（这可能） [英] A search withina comma seperated list (Is this possible)

问题描述

我有一个mysql数据库，其中包含提供特定

产品的公司列表


tblSuppliers（简体）


sID | sName | goodsRefs

1 | comp名称| 1,2,3,4,5

2 |公司2 | 2,4

tblGoods（简体）


gID | gName

1 |鱼

2 |面包

3 |苹果

4 |香蕉

5 |巧克力

想法是逗号分隔列表与他们提供的商品有关


是否可以向所有提供面包的供应商展示？


我知道IN语法，但这只能使用SELECT *来自tblGoods

WHERE gID in（1,2）


如何创建一个SELECT语句以显示包含ID的公司

2在各自的逗号分隔列表中


任何帮助都将非常感谢


Craig

I have a mysql database with a list of companies who supply specific
products

tblSuppliers (simplified)

sID | sName | goodsRefs
1 | comp name | 1,2,3,4,5
2 | company 2 | 2,4
tblGoods (simplified)

gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate
The idea is that the comma seperated list relates to the goods they supply

is it possible to show all suppliers who provide bread?

I know of the IN syntax but this only works using SELECT * from tblGoods
WHERE gID in (1,2)

How do i create a SELECT Statement to show the companies containing the ID
2 in their respective comma separted list

Any help would be gratefully appreciated

Craig

推荐答案

Craig Keightley写道：

Craig Keightley wrote:

我有一个mysql数据库提供特定产品的公司名单

tblSuppliers（简体）

sID | sName | goodsRefs
1 | comp名称| 1,2,3,4,5
2 |公司2 | 2,4

tblGoods（简体）

gID | gName
1 |鱼
2 |面包
3 |苹果
4 |香蕉
5 |巧克力

我们的想法是，逗号分隔列表与他们提供的商品有关

是否可以向所有提供面包的供应商展示？


SELECT * FROM yourtable WHERE goodsRefs LIKE''％2％'';


如果您有20个或更多商品，那么查询会产生错误的结果，但是

那么你可以用一把手来构建一个冗长而复杂的查询。


我知道IN语法，但这只能起作用使用SELECT * from tblGoods
WHERE gID in（1,2）


IN（）确实接受一个handfull参数并将它们与列进行比较，

值必须是同一类型，因为你的goodsRefs是varchar，你不能比较

它到int。


我如何创建一个SELECT语句，在各自的逗号分隔列表中显示包含ID
2的公司

I have a mysql database with a list of companies who supply specific
products

tblSuppliers (simplified)

sID | sName | goodsRefs
1 | comp name | 1,2,3,4,5
2 | company 2 | 2,4
tblGoods (simplified)

gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate
The idea is that the comma seperated list relates to the goods they supply

is it possible to show all suppliers who provide bread?
SELECT * FROM yourtable WHERE goodsRefs LIKE ''%2%'';

If you have 20 or more goods, then the query would generate false results, but
then you can build a long and complicated query with a handfull OR.

I know of the IN syntax but this only works using SELECT * from tblGoods
WHERE gID in (1,2)
IN() does take a handfull arguments and compares them to the column, the
values has to be of same type, as your goodsRefs is varchar, you can''t compare
it to int.

How do i create a SELECT Statement to show the companies containing the ID
2 in their respective comma separted list

您通常会构建3个表

tblSuppliers（简化）

sID | sName

1 | comp名称

2 |公司2


tblGoods（简体）

gID | gName

1 |鱼

2 |面包

3 |苹果

4 |香蕉

5 |巧克力


tblRefs

sID | gID

1 | 1

1 | 2

1 | 3

1 | 4

1 | 5

2 | 2

2 | 4


然后当你想要一些东西时加入表格
http://dev.mysql.com/doc/mysql/en/join.html


SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =

tblRefs.sID AND tblRefs.sID =''2'';


或者如果你搜索不止一个，比如说巧克力和香蕉


SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =

tblRefs.sID AND tblRefs.sID IN（4， 5）;


// Aho

You usually build 3 tables

tblSuppliers (simplified)
sID | sName
1 | comp name
2 | company 2

tblGoods (simplified)
gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate

tblRefs
sID | gID
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
2 | 2
2 | 4

You then join the tables when you want something out of them
http://dev.mysql.com/doc/mysql/en/join.html

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID=''2'';

or if you search for more than one, say chocolate and bananas

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID IN(4,5);

//Aho

SELECT * FROM yourtable WHERE goodsRefs LIKE'' ％2％ '';


我已经尝试了这个beofe，列表可能超过500

你通常会建3个桌子


不是那么啰嗦吗？

供应商将通过网络界面添加，他们可以在这里添加一个

供应商并选择他们提供的产品，第3个可能不起作用


" JO AHO" <我们** @ example.net>在消息中写道

news：39 ************* @ individual.net ... Craig Keightley写道：

SELECT * FROM yourtable WHERE goodsRefs LIKE ''%2%'';
I''ve tried that beofe and the list could have in excess of 500
You usually build 3 tables
isn''t that long winded?
The suppliers will be added via a web interface, where they can add a
supplier and choose what products they provide, the 3rd able may not work

"J.O. Aho" <us**@example.net> wrote in message
news:39*************@individual.net... Craig Keightley wrote:

我有一个mysql数据库与提供特定产品的公司列表

tblSuppliers（简化）

sID | sName | goodsRefs
1 | comp名称| 1,2,3,4,5
2 |公司2 | 2,4

tblGoods（简体）

gID | gName
1 |鱼
2 |面包
3 |苹果
4 |香蕉
5 |巧克力

我们的想法是，逗号分隔列表与他们提供的商品有关

是否可以向所有提供面包的供应商展示？

I have a mysql database with a list of companies who supply specific
products

tblSuppliers (simplified)

sID | sName | goodsRefs
1 | comp name | 1,2,3,4,5
2 | company 2 | 2,4
tblGoods (simplified)

gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate
The idea is that the comma seperated list relates to the goods they
supply

is it possible to show all suppliers who provide bread?

SELECT * FROM yourtable WHERE goodsRefs LIKE''％2％'';

如果您有20个或更多商品，那么查询会产生错误结果，但是你可以使用一个或多个OR来构建一个冗长而复杂的查询。

SELECT * FROM yourtable WHERE goodsRefs LIKE ''%2%'';

If you have 20 or more goods, then the query would generate false results,
but then you can build a long and complicated query with a handfull OR.

我知道IN语法，但这只能使用来自tblGoods的SELECT *
（1,2）中的哪个gID

I know of the IN syntax but this only works using SELECT * from tblGoods
WHERE gID in (1,2)

IN（）确实采用了一个handfull参数并将它们与列进行比较，
值必须是相同的类型，如你的goodsRefs是varchar，你不能将它与int进行比较。

IN() does take a handfull arguments and compares them to the column, the
values has to be of same type, as your goodsRefs is varchar, you can''t
compare it to int.

我如何创建一个SELECT语句来显示包含 ID 2在各自的逗号分隔列表中

How do i create a SELECT Statement to show the companies containing the
ID 2 in their respective comma separted list

您通常会构建3个表格
tblSuppliers（简化）
sID | sName
1 | comp name
2 |公司2

tblGoods（简体）
gID | gName
1 |鱼
2 |面包
3 |苹果
4 |香蕉
5 |巧克力

tblRefs
sID | gID
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
2 | 2
2 | 4

当你想要一些东西时，你就加入了这些桌子
http://dev.mysql.com/doc/mysql/en/join.html
SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers .sID =
tblRefs.sID和tblRefs.sID =''2'';

或者如果你搜索多个，说巧克力和香蕉

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID IN（4,5）;

// Aho

You usually build 3 tables

tblSuppliers (simplified)
sID | sName
1 | comp name
2 | company 2

tblGoods (simplified)
gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate

tblRefs
sID | gID
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
2 | 2
2 | 4

You then join the tables when you want something out of them
http://dev.mysql.com/doc/mysql/en/join.html

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID=''2'';

or if you search for more than one, say chocolate and bananas

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID IN(4,5);

//Aho

Craig Keightley写道：

Craig Keightley wrote:

我有一个mysql数据库，其中包含提供特定产品的公司列表

tblSuppliers（简化） ）

sID | sName | goodsRefs
1 | comp名称| 1,2,3,4,5
2 |公司2 | 2,4

I have a mysql database with a list of companies who supply specific
products

tblSuppliers (simplified)

sID | sName | goodsRefs
1 | comp name | 1,2,3,4,5
2 | company 2 | 2,4

在表中的同一个字段中有多个数据是一个不好的想法，原因是BAD

的想法你正在发现将每个条目放在

单独的行上，或者更好（为了避免重复sName），安排

你的数据库如下：


tblSuppliers：sID，sName


tblSupplierGoods：sID，gID


tblGoods：gID，gName


即每家公司在tblSuppliers中有一个条目，将其名称与

ID相关联，然后在tblSupplierGoods中为每个单独的商品ID

提供一个条目。


要查找哪些制造商提供面包，您的查询将是：


SELECT tblSuppliers。* FROM

tblSuppliers ，tblSupplierGoods，tblGoods

WHERE

tblSupplierGoods.sID = tblSuppliers.sID

AND tblSupplierGoods.gID = tblGoods.gID

AND tblGoods.gName =''bread''

-

Oli

Having multiple pieces of data in the same field in a table is a BAD
idea, for reasons that you are discovering. Have each entry on a
separate line, or even better (to avoid duplication of sName), arrange
your db as so:

tblSuppliers: sID, sName

tblSupplierGoods: sID, gID

tblGoods: gID, gName

i.e. each company has ONE entry in tblSuppliers linking their name to an
ID, and then an entry in tblSupplierGoods for each individual goods ID
that they supply.

To find which manufacturers supply bread, your query would be:

SELECT tblSuppliers.* FROM
tblSuppliers, tblSupplierGoods, tblGoods
WHERE
tblSupplierGoods.sID = tblSuppliers.sID
AND tblSupplierGoods.gID = tblGoods.gID
AND tblGoods.gName = ''bread''
--
Oli

这篇关于搜索withina逗号分隔列表（这可能）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！