多列数据库单列去重及Excel模拟用户购物项分配技术问询

Hey there! Let's break down your two questions with practical, actionable solutions tailored to your scenarios:

First off, always back up your data before deleting anything—this is non-negotiable to avoid accidental data loss. Here's how to approach it:

Step 1: Identify Duplicate Data First #

Run a query to confirm which entries are duplicates in your target column. This helps you verify what you're about to delete:

SELECT target_column, COUNT(*)
FROM your_table
GROUP BY target_column
HAVING COUNT(*) > 1;

Replace target_column with your column name and your_table with your table name.

Step 2: Delete Duplicates (Keep One Instance) #

The exact syntax varies by database, but using window functions is the most reliable method for modern databases:

For MySQL (8.0+):

DELETE FROM your_table
WHERE primary_key_column NOT IN (
    SELECT primary_key_column
    FROM (
        SELECT primary_key_column,
               ROW_NUMBER() OVER (PARTITION BY target_column ORDER BY primary_key_column) AS rn
        FROM your_table
    ) AS temp_table
    WHERE rn = 1
);

• PARTITION BY target_column groups rows by your duplicate column
• ORDER BY primary_key_column ensures you keep the earliest (or latest, if you reverse the order) instance
• Replace primary_key_column with your table's unique identifier (like an ID column)

For SQL Server / PostgreSQL:

Use a CTE (Common Table Expression) for cleaner code:

WITH DuplicateCTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY target_column ORDER BY primary_key_column) AS rn
    FROM your_table
)
DELETE FROM DuplicateCTE WHERE rn > 1;

If your table doesn't have a primary key, you can use a combination of columns that uniquely identify rows, or temporarily add an auto-increment ID column to make this process easier.

You've got a list of 300 items and need to assign 0-100 unique items per user, with varying counts. Here are two methods:

Method 1: Formula-Based Approach (No Code Needed, Excel 365/2021) #

Assume:

• Your 300 items are in Sheet2!A2:A301 (A1 is header "Item Name")
• Your 1000 users are in Sheet1!A2:A1001 (A1 is header "User ID")

1. Generate Random Item Counts:
   In Sheet1!B2 (drag down to B1001), use:

   =RANDBETWEEN(0,100)
   

   This gives each user a random number of items (0 to 100).

2. Assign Unique Random Items:
   In Sheet1!C2 (drag down to C1001), use this dynamic array formula:

   =IF(B2=0,"",TEXTJOIN(", ",TRUE,INDEX(Sheet2!$A$2:$A$301,UNIQUE(RANDBETWEEN(ROW(INDIRECT("1:"&ROWS(Sheet2!$A$2:$A$301))),ROWS(Sheet2!$A$2:$A$301),B2)))))
   

   • RANDBETWEEN generates random indices for your item list
   • UNIQUE ensures no duplicate items per user
   • INDEX pulls the corresponding item names
   • TEXTJOIN combines items into a single comma-separated string

Method 2: VBA Script (Faster for Large Datasets) #

If you're using an older Excel version or want to automate the process fully:

1. Press Alt + F11 to open the VBA Editor
2. Insert a new Module (Right-click your workbook > Insert > Module)
3. Paste this code:

Sub AssignRandomShoppingItems()
    Dim userSheet As Worksheet, itemSheet As Worksheet
    Dim lastUserRow As Long, lastItemRow As Long
    Dim userIndex As Long, itemCount As Long
    Dim itemList As Variant, selectedItems As Collection
    Dim randomPos As Integer, i As Integer
    
    ' Update sheet names to match your workbook
    Set userSheet = ThisWorkbook.Sheets("Sheet1")
    Set itemSheet = ThisWorkbook.Sheets("Sheet2")
    
    lastUserRow = userSheet.Cells(Rows.Count, "A").End(xlUp).Row
    lastItemRow = itemSheet.Cells(Rows.Count, "A").End(xlUp).Row
    itemList = itemSheet.Range("A2:A" & lastItemRow).Value ' Load items into memory
    
    ' Loop through each user
    For userIndex = 2 To lastUserRow
        ' Generate random item count (0 to 100)
        itemCount = Int((101) * Rnd)
        
        If itemCount > 0 Then
            Set selectedItems = New Collection
            ' Pick unique items until we reach the desired count
            Do While selectedItems.Count < itemCount
                randomPos = Int((lastItemRow - 1) * Rnd) + 1 ' Random index from 1 to item count
                On Error Resume Next ' Ignore duplicate key errors
                selectedItems.Add itemList(randomPos, 1), Key:=CStr(itemList(randomPos, 1))
                On Error GoTo 0
            Loop
            
            ' Combine items into a string
            Dim itemString As String
            itemString = ""
            For i = 1 To selectedItems.Count
                itemString = itemString & selectedItems(i) & ", "
            Next i
            userSheet.Cells(userIndex, "C").Value = Left(itemString, Len(itemString) - 2) ' Trim trailing comma
        Else
            userSheet.Cells(userIndex, "C").Value = "" ' Empty if no items
        End If
        
        ' Write the item count to column B
        userSheet.Cells(userIndex, "B").Value = itemCount
    Next userIndex
    
    MsgBox "Shopping items assigned successfully!"
End Sub

4. Run the macro (Press F5 in the editor, or add a button to your sheet for easy access)

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