如何根据表中的用户名在下拉列表中绑定SQL数据库列表 [英] How do I bind list of SQL databases in a dropdown as per username in table

问题描述

假设有5个数据库。



每个数据库都是user_details表。



例如用户名= user12，存在于3个数据库中。



因此当用户12登录时，它应该显示他在下拉列表中列出的3个数据库。知道如何实现它吗？请输入任何输入。

Suppose there are 5 databases.

Each database as user_details table.

For example username = user12, is exists in 3 databases.

So when user12 logs in, it should show the 3 databases where he is listed in a dropdown. Any idea how to achieve it? Please any inputs will be helpful.

推荐答案

一个选项是使用CTE查询Fife数据库中的每个Users表，如下所示：

One option would be to use a CTE to query every individual Users table in the Fife databases like so:

WITH AllUsers AS (
	SELECT ID, Alias, FirstName, LastName, 'Test1' AS 'DBName' 
		FROM Test1..Users
		WHERE Alias = 'user12'
	UNION
	SELECT ID, Alias, FirstName, LastName, 'Test2' AS 'DBName' 
		FROM Test2..Users
		WHERE Alias = 'user12'
	UNION
	SELECT ID, Alias, FirstName, LastName, 'Test3' AS 'DBName' 
		FROM Test3..Users
		WHERE Alias = 'user12'
	UNION
	SELECT ID, Alias, FirstName, LastName, 'Test4' AS 'DBName' 
		FROM Test4..Users
		WHERE Alias = 'user12'
	UNION
	SELECT ID, Alias, FirstName, LastName, 'Test5' AS 'DBName' 
		FROM Test5..Users
		WHERE Alias = 'user12'
)
SELECT ID, Alias, DBName FROM AllUsers;

如果用户不存在于数据库Test3 / 4/5 ..Users表中，结果是wou ld是：

If the user doesn't exist in database Test3/4/5..Users table the result would be:

ID	Alias	DBName
3	user12	Test1
3	user12	Test2

您还可以考虑使用函数或过程，您可以循环访问数据库或在其中一个数据库中创建包含其他元数据的查找表，您可以跟踪各个数据库中的所有用户。



希望这会对你有所帮助。



Harold

You could also consider using a function or procedure so you can loop through the databases or make a lookup table with additional meta-data in one of the databases where you keep track of all the users in the various databases.

Hope this helps you.

Harold

这篇关于如何根据表中的用户名在下拉列表中绑定SQL数据库列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！