如何编写查询语句获取被分配多个不同Maincode的物料列表？

Hey, let's figure out why your query is returning extra results and fix it!

Your original query uses COUNT(*) which counts all records for a material, not the number of distinct Maincode values assigned to it. That's why it's picking up Material4 even if you think it shouldn't (wait, looking at your sample data, Material4 has Main1 and Main4—those are two distinct codes, so it should be included. Maybe there was a typo in your sample or description? But regardless, let's get the query right for your core need: finding materials assigned to multiple different Maincodes.

Fixed Query #

SELECT M.MaterialCode
FROM #MaterialMainCodetemp M
GROUP BY M.MaterialCode
HAVING COUNT(DISTINCT M.Maincode) > 1;

What's Changed? #

• COUNT(DISTINCT M.Maincode) first removes duplicate Maincode entries for each material, then counts the unique ones. This ensures we only count different Maincodes, not just how many times the material appears in the table.
• In your sample data:
  • Material1 has 2 distinct Maincodes (Main1, Main2) → gets returned.
  • Material4 also has 2 distinct Maincodes (Main1, Main4) → would also be returned. If you intended for Material4 to not be included, double-check your sample data—maybe those two entries have the same Maincode? If so, this fixed query will exclude it correctly.

内容的提问来源于stack exchange，提问作者user3799325